<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1.0,viewport-fit=cover"><title>mysql学习 | mundane</title><meta name="author" content="frode"><meta name="copyright" content="frode"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="mysql基本语法DDL DML DQL DCL 执行顺序 from ​   表名列表 where ​   条件列表 group by ​   分组字段列表 having ​   分组后条件列表 select ​   字段列表 order by ​   排序字段列表 limit ​   分页参数 create table emp(    id int                  commen">
<meta property="og:type" content="article">
<meta property="og:title" content="mysql学习">
<meta property="og:url" content="https://gitee.com/frode117/2023/04/27/mysql-study/index.html">
<meta property="og:site_name" content="mundane">
<meta property="og:description" content="mysql基本语法DDL DML DQL DCL 执行顺序 from ​   表名列表 where ​   条件列表 group by ​   分组字段列表 having ​   分组后条件列表 select ​   字段列表 order by ​   排序字段列表 limit ​   分页参数 create table emp(    id int                  commen">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/img/suzume.png">
<meta property="article:published_time" content="2023-04-27T15:01:33.000Z">
<meta property="article:modified_time" content="2023-04-28T13:39:53.350Z">
<meta property="article:author" content="frode">
<meta property="article:tag" content="mysql">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/img/suzume.png"><link rel="shortcut icon" href="/mundane/img/ai.jpg"><link rel="canonical" href="https://gitee.com/frode117/2023/04/27/mysql-study/index.html"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/mundane/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox/fancybox.min.css" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = { 
  root: '/mundane/',
  algolia: undefined,
  localSearch: {"path":"/mundane/search.xml","languages":{"hits_empty":"找不到您查询的内容：${query}","hits_stats":"共找到 ${hits} 篇文章"}},
  translate: undefined,
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false},
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '',
  dateSuffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: undefined,
  lightbox: 'fancybox',
  Snackbar: {"chs_to_cht":"你已切换为繁体","cht_to_chs":"你已切换为简体","day_to_night":"你已切换为深色模式","night_to_day":"你已切换为浅色模式","bgLight":"#49b1f5","bgDark":"#1f1f1f","position":"bottom-right"},
  source: {
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery/dist/fjGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery/dist/fjGallery.min.css'
    }
  },
  isPhotoFigcaption: false,
  islazyload: true,
  isAnchor: false,
  percent: {
    toc: true,
    rightside: false,
  }
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = {
  title: 'mysql学习',
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2023-04-28 21:39:53'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
    win.getCSS = (url,id = false) => new Promise((resolve, reject) => {
      const link = document.createElement('link')
      link.rel = 'stylesheet'
      link.href = url
      if (id) link.id = id
      link.onerror = reject
      link.onload = link.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        link.onload = link.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(link)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    
    const detectApple = () => {
      if(/iPad|iPhone|iPod|Macintosh/.test(navigator.userAgent)){
        document.documentElement.classList.add('apple')
      }
    }
    detectApple()
    })(window)</script><svg aria-hidden="true" style="position:absolute; overflow:hidden; width:0; height:0"><symbol id="icon-sun" viewBox="0 0 1024 1024"><path d="M960 512l-128 128v192h-192l-128 128-128-128H192v-192l-128-128 128-128V192h192l128-128 128 128h192v192z" fill="#FFD878" p-id="8420"></path><path d="M736 512a224 224 0 1 0-448 0 224 224 0 1 0 448 0z" fill="#FFE4A9" p-id="8421"></path><path d="M512 109.248L626.752 224H800v173.248L914.752 512 800 626.752V800h-173.248L512 914.752 397.248 800H224v-173.248L109.248 512 224 397.248V224h173.248L512 109.248M512 64l-128 128H192v192l-128 128 128 128v192h192l128 128 128-128h192v-192l128-128-128-128V192h-192l-128-128z" fill="#4D5152" p-id="8422"></path><path d="M512 320c105.888 0 192 86.112 192 192s-86.112 192-192 192-192-86.112-192-192 86.112-192 192-192m0-32a224 224 0 1 0 0 448 224 224 0 0 0 0-448z" fill="#4D5152" p-id="8423"></path></symbol><symbol id="icon-moon" viewBox="0 0 1024 1024"><path d="M611.370667 167.082667a445.013333 445.013333 0 0 1-38.4 161.834666 477.824 477.824 0 0 1-244.736 244.394667 445.141333 445.141333 0 0 1-161.109334 38.058667 85.077333 85.077333 0 0 0-65.066666 135.722666A462.08 462.08 0 1 0 747.093333 102.058667a85.077333 85.077333 0 0 0-135.722666 65.024z" fill="#FFB531" p-id="11345"></path><path d="M329.728 274.133333l35.157333-35.157333a21.333333 21.333333 0 1 0-30.165333-30.165333l-35.157333 35.157333-35.114667-35.157333a21.333333 21.333333 0 0 0-30.165333 30.165333l35.114666 35.157333-35.114666 35.157334a21.333333 21.333333 0 1 0 30.165333 30.165333l35.114667-35.157333 35.157333 35.157333a21.333333 21.333333 0 1 0 30.165333-30.165333z" fill="#030835" p-id="11346"></path></symbol></svg><meta name="generator" content="Hexo 6.3.0"><link rel="alternate" href="/mundane/atom.xml" title="mundane" type="application/atom+xml">
</head><body><div id="web_bg"></div><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="avatar-img is-center"><img src="/mundane/img/violet.jpg" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="sidebar-site-data site-data is-center"><a href="/mundane/archives/"><div class="headline">文章</div><div class="length-num">4</div></a><a href="/mundane/tags/"><div class="headline">标签</div><div class="length-num">8</div></a><a href="/mundane/categories/"><div class="headline">分类</div><div class="length-num">4</div></a></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/mundane/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/mundane/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></div><div class="menus_item"><a class="site-page" href="/mundane/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/mundane/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/mundane/messageboard/"><i class="fa-fw fa fa-paper-plane"></i><span> 留言板</span></a></div><div class="menus_item"><a class="site-page" href="/mundane/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw fa fa-list"></i><span> 菜单</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/mundane/myself/"><i class="fa-fw fa fa-id-card"></i><span> myself</span></a></li><li><a class="site-page child" target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly/"><i class="fa-fw fa fa-heart"></i><span> butterfly主题</span></a></li></ul></div></div></div></div><div class="post" id="body-wrap"><header class="post-bg" id="page-header" style="background-image: url('https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/img/suzume.png')"><nav id="nav"><span id="blog-info"><a href="/mundane/" title="mundane"><span class="site-name">mundane</span></a></span><div id="menus"><div id="search-button"><a class="site-page social-icon search" href="javascript:void(0);"><i class="fas fa-search fa-fw"></i><span> 搜索</span></a></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/mundane/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/mundane/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></div><div class="menus_item"><a class="site-page" href="/mundane/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/mundane/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/mundane/messageboard/"><i class="fa-fw fa fa-paper-plane"></i><span> 留言板</span></a></div><div class="menus_item"><a class="site-page" href="/mundane/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw fa fa-list"></i><span> 菜单</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/mundane/myself/"><i class="fa-fw fa fa-id-card"></i><span> myself</span></a></li><li><a class="site-page child" target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly/"><i class="fa-fw fa fa-heart"></i><span> butterfly主题</span></a></li></ul></div></div><div id="toggle-menu"><a class="site-page" href="javascript:void(0);"><i class="fas fa-bars fa-fw"></i></a></div></div></nav><div id="post-info"><h1 class="post-title">mysql学习</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2023-04-27T15:01:33.000Z" title="发表于 2023-04-27 23:01:33">2023-04-27</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2023-04-28T13:39:53.350Z" title="更新于 2023-04-28 21:39:53">2023-04-28</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/mundane/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-wordcount"><i class="far fa-file-word fa-fw post-meta-icon"></i><span class="post-meta-label">字数总计:</span><span class="word-count">11.8k</span><span class="post-meta-separator">|</span><i class="far fa-clock fa-fw post-meta-icon"></i><span class="post-meta-label">阅读时长:</span><span>47分钟</span></span><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title="mysql学习"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">阅读量:</span><span id="busuanzi_value_page_pv"><i class="fa-solid fa-spinner fa-spin"></i></span></span></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="post-content" id="article-container"><h1 id="mysql"><a href="#mysql" class="headerlink" title="mysql"></a>mysql</h1><h2 id="基本语法"><a href="#基本语法" class="headerlink" title="基本语法"></a>基本语法</h2><p>DDL</p>
<p>DML</p>
<p>DQL</p>
<p>DCL</p>
<h3 id="执行顺序"><a href="#执行顺序" class="headerlink" title="执行顺序"></a>执行顺序</h3><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230315212119833.png" alt="image-20230315212119833"></p>
<p>from</p>
<p>​   表名列表</p>
<p>where</p>
<p>​   条件列表</p>
<p>group by</p>
<p>​   分组字段列表</p>
<p>having</p>
<p>​   分组后条件列表</p>
<p>select</p>
<p>​   字段列表</p>
<p>order by</p>
<p>​   排序字段列表</p>
<p>limit</p>
<p>​   分页参数</p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">create table emp(</span><br><span class="line"></span><br><span class="line">    id int                  comment &#x27;编号&#x27;,</span><br><span class="line">    workno varchar(10)      comment &#x27;工号&#x27;,</span><br><span class="line">    name varchar(10)        comment &#x27;姓名&#x27;,</span><br><span class="line">    gender char(1)          comment &#x27;性别&#x27;,</span><br><span class="line">    age tinyint unsigned    comment &#x27;年龄&#x27;,</span><br><span class="line">    idcard char(18)         comment &#x27;身份证号&#x27;,</span><br><span class="line">    workaddress varchar(50) comment &#x27;工作地址&#x27;,</span><br><span class="line">    entrydate date          comment &#x27;入职时间&#x27;</span><br><span class="line">)comment &#x27;员工表&#x27;;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">insert into emp (id,workno, name, gender, age, idcard, workaddress, entrydate)</span><br><span class="line">values  (1, &#x27;1&#x27;, &#x27;柳岩&#x27;, &#x27;女&#x27;, 20, &#x27;123456789012345678&#x27;, &#x27;北京&#x27;, &#x27;2000-01-01&#x27;),</span><br><span class="line">        (2, &#x27;2&#x27;, &#x27;张无忌&#x27;, &#x27;男&#x27;, 18, &#x27;123456789012345670&#x27;, &#x27;北京&#x27;, &#x27;2005-09-01&#x27;),</span><br><span class="line">        (3, &#x27;3&#x27;, &#x27;韦一笑&#x27;, &#x27;男&#x27;, 38, &#x27;123456789712345670&#x27;, &#x27;上海&#x27;, &#x27;2005-08-01&#x27;),</span><br><span class="line">        (4, &#x27;4&#x27;, &#x27;赵敏&#x27;, &#x27;女&#x27;, 18, &#x27;123456757123845670&#x27;, &#x27;北京&#x27;, &#x27;2009-12-01&#x27;),</span><br><span class="line">        (5, &#x27;5&#x27;, &#x27;小昭&#x27;, &#x27;女&#x27;, 16, &#x27;123456769012345678&#x27;, &#x27;上海&#x27;, &#x27;2007-07-01&#x27;),</span><br><span class="line">        (6, &#x27;6&#x27;, &#x27;杨逍&#x27;, &#x27;男&#x27;, 28, &#x27;12345678931234567X&#x27;, &#x27;北京&#x27;, &#x27;2006-01-01&#x27;),</span><br><span class="line">        (7, &#x27;7&#x27;, &#x27;范瑶&#x27;, &#x27;男&#x27;, 40, &#x27;123456789212345670&#x27;, &#x27;北京&#x27;, &#x27;2005-05-01&#x27;),</span><br><span class="line">        (8, &#x27;8&#x27;, &#x27;黛绮丝&#x27;, &#x27;女&#x27;, 38, &#x27;123456157123645670&#x27;, &#x27;天津&#x27;, &#x27;2015-05-01&#x27;),</span><br><span class="line">        (9, &#x27;9&#x27;, &#x27;范凉凉&#x27;, &#x27;女&#x27;, 45, &#x27;123156789012345678&#x27;, &#x27;北京&#x27;, &#x27;2010-04-01&#x27;),</span><br><span class="line">        (10, &#x27;10&#x27;, &#x27;陈友谅&#x27;, &#x27;男&#x27;, 53, &#x27;123456789012345670&#x27;, &#x27;上海&#x27;, &#x27;2011-01-01&#x27;),</span><br><span class="line">        (11, &#x27;11&#x27;, &#x27;张士诚&#x27;, &#x27;男&#x27;, 55, &#x27;123567897123465670&#x27;, &#x27;江苏&#x27;, &#x27;2015-05-01&#x27;),</span><br><span class="line">        (12, &#x27;12&#x27;, &#x27;常遇春&#x27;, &#x27;男&#x27;, 32, &#x27;123446757152345670&#x27;, &#x27;北京&#x27;, &#x27;2004-02-01&#x27;),</span><br><span class="line">        (13, &#x27;13&#x27;, &#x27;张三丰&#x27;, &#x27;男&#x27;, 88, &#x27;123656789012345678&#x27;, &#x27;江苏&#x27;, &#x27;2020-11-01&#x27;),</span><br><span class="line">        (14, &#x27;14&#x27;, &#x27;灭绝&#x27;, &#x27;女&#x27;, 65, &#x27;123456719012345670&#x27;, &#x27;西安&#x27;, &#x27;2019-05-01&#x27;),</span><br><span class="line">        (15, &#x27;15&#x27;, &#x27;胡青牛&#x27;, &#x27;男&#x27;, 70, &#x27;12345674971234567X&#x27;, &#x27;西安&#x27;, &#x27;2018-04-01&#x27;),</span><br><span class="line">        (16, &#x27;16&#x27;, &#x27;周芷若&#x27;, &#x27;女&#x27;, 18, null, &#x27;北京&#x27;, &#x27;2012-06-01&#x27;);</span><br><span class="line"></span><br><span class="line">select name,workno,age from emp;</span><br><span class="line"></span><br><span class="line">select * from emp;</span><br><span class="line"></span><br><span class="line">select distinct workaddress &#x27;工作地址&#x27; from emp;</span><br><span class="line"></span><br><span class="line">select * from emp where age = 88;</span><br><span class="line"></span><br><span class="line">select * from emp where age &lt;= 20;</span><br><span class="line"></span><br><span class="line">select * from emp where idcard is NULL;</span><br><span class="line"></span><br><span class="line">select * from emp where idcard is not null;</span><br><span class="line"></span><br><span class="line">select * from emp where age != 88;</span><br><span class="line"></span><br><span class="line">select * from emp where age &lt;&gt; 88;</span><br><span class="line"></span><br><span class="line">select * from emp where age between 15 and 20;</span><br><span class="line"></span><br><span class="line">select * from emp where age != 88;</span><br><span class="line"></span><br><span class="line">select * from emp where gender= &#x27;女&#x27; and age &lt; 25;</span><br><span class="line"></span><br><span class="line">select * from emp where age = 18 or age = 20 or age = 40;</span><br><span class="line">select * from emp where age in (18,20,40);</span><br><span class="line"></span><br><span class="line">select * from emp where name like &#x27;__&#x27;;</span><br><span class="line"></span><br><span class="line">select * from emp where idcard like &#x27;%X&#x27;;</span><br><span class="line"></span><br><span class="line">-- 聚合函数</span><br><span class="line">select count(*) from emp;</span><br><span class="line">select count(idcard) from emp; -- null值不参与计算</span><br><span class="line"></span><br><span class="line">select avg(age) from emp;</span><br><span class="line"></span><br><span class="line">select max(age) from emp;</span><br><span class="line">select min(age) from emp;</span><br><span class="line"></span><br><span class="line">select sum(age) from emp where workaddress = &#x27;西安&#x27;;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">-- DQL 分组查询</span><br><span class="line">select gender, count(*) from emp group by gender;</span><br><span class="line"></span><br><span class="line">select gender, avg(age) from emp group by gender;</span><br><span class="line"></span><br><span class="line">-- 查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址</span><br><span class="line">select workaddress, count(*) address_count from emp where age &lt; 45 group by workaddress having address_count &gt;= 3;</span><br><span class="line"></span><br><span class="line">-- 排序查询</span><br><span class="line">select * from emp order by age desc ;</span><br><span class="line"></span><br><span class="line">select * from emp order by entrydate desc ;</span><br><span class="line"></span><br><span class="line">select * from emp order by age, entrydate desc ;</span><br><span class="line"></span><br><span class="line">-- 分页查询</span><br><span class="line">select * from emp limit 0,10;</span><br><span class="line"></span><br><span class="line">select * from emp limit 10,10;</span><br><span class="line"></span><br><span class="line">select * from emp where age in (20,21,22,23);</span><br><span class="line">select * from emp where age between 20 and 23;</span><br><span class="line"></span><br><span class="line">select * from emp where gender = &#x27;男&#x27; and (age between 20 and 40) and name like &#x27;___&#x27;;</span><br><span class="line"></span><br><span class="line">select gender, count(*) from emp where age &lt; 60 group by gender;</span><br><span class="line"></span><br><span class="line">select name,age from emp where age &lt;= 35 order by age,entrydate desc ;</span><br><span class="line"></span><br><span class="line">select * from emp where gender = &#x27;男&#x27; and (age between 20 and 40) order by age, entrydate limit 5;</span><br><span class="line"></span><br><span class="line">select e.name,e.age from emp e where e.age &gt; 15 order by age;</span><br><span class="line"></span><br><span class="line">select e.name ename,e.age eage from emp e where e.age &gt; 15 order by eage;</span><br><span class="line"></span><br><span class="line">-- from</span><br><span class="line">-- where</span><br><span class="line">-- select</span><br><span class="line">-- order by</span><br><span class="line">-- limit</span><br><span class="line"></span><br><span class="line">-- DCL 管理用户</span><br><span class="line">use mysql;</span><br><span class="line">select * from user;</span><br><span class="line"></span><br><span class="line">create user &#x27;itcast&#x27;@&#x27;localhost&#x27; identified by &#x27;123456&#x27;;</span><br><span class="line"></span><br><span class="line">create user &#x27;heima&#x27;@&#x27;%&#x27; identified by &#x27;123456&#x27;;</span><br><span class="line"></span><br><span class="line">alter user &#x27;heima&#x27;@&#x27;%&#x27; identified with mysql_native_password by &#x27;1234&#x27;;</span><br><span class="line"></span><br><span class="line">drop user &#x27;itcast&#x27;@&#x27;localhost&#x27;;</span><br><span class="line">drop user &#x27;guest&#x27;@&#x27;%&#x27;;</span><br><span class="line"></span><br><span class="line">-- 查询权限</span><br><span class="line">show grants for &#x27;heima&#x27;@&#x27;%&#x27;;</span><br><span class="line"></span><br><span class="line">-- 授予权限</span><br><span class="line">grant all on itcast.* to &#x27;heima&#x27;@&#x27;%&#x27;;</span><br><span class="line">grant all on test.* to &#x27;heima&#x27;@&#x27;%&#x27;;</span><br><span class="line"></span><br><span class="line">-- 撤销权限</span><br><span class="line">revoke all on itcast.* from &#x27;heima&#x27;@&#x27;%&#x27;;</span><br></pre></td></tr></table></figure>

<h2 id="函数"><a href="#函数" class="headerlink" title="函数"></a>函数</h2><p>字符串函数</p>
<p>数值函数</p>
<p>日期函数</p>
<p>流程函数</p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">-- 字符串函数</span><br><span class="line">select concat(&#x27;hello&#x27;,&#x27;mysql&#x27;);</span><br><span class="line"></span><br><span class="line">select lower(&#x27;HELLO&#x27;);</span><br><span class="line"></span><br><span class="line">select upper(&#x27;hello,mysql&#x27;);</span><br><span class="line"></span><br><span class="line">select lpad(&#x27;01&#x27;,5, &#x27;-&#x27;);</span><br><span class="line">select rpad(&#x27;01&#x27;,5, &#x27;-&#x27;);</span><br><span class="line"></span><br><span class="line">select trim(&#x27; hello mysql &#x27;);</span><br><span class="line"></span><br><span class="line">select substring(&#x27;hello mysql&#x27;, 1, 5)</span><br><span class="line"></span><br><span class="line">use test;</span><br><span class="line"></span><br><span class="line">update emp set workno = lpad(workno, 5, &#x27;0&#x27;);</span><br><span class="line"></span><br><span class="line">-- 数值函数</span><br><span class="line">select ceil(1.5);</span><br><span class="line"></span><br><span class="line">select floor(1.5);</span><br><span class="line"></span><br><span class="line">select mod(3,4);</span><br><span class="line"></span><br><span class="line">select rand();</span><br><span class="line"></span><br><span class="line">select round(2.345, 2);</span><br><span class="line"></span><br><span class="line">-- 六位数随机验证码</span><br><span class="line">select lpad(round(rand()*1000000, 0), 6, &#x27;0&#x27;);</span><br><span class="line"></span><br><span class="line">-- 日期函数</span><br><span class="line">select curdate();</span><br><span class="line">select curtime();</span><br><span class="line">select now();</span><br><span class="line"></span><br><span class="line">select year(now());</span><br><span class="line">select month(now());</span><br><span class="line">select day(now());</span><br><span class="line"></span><br><span class="line">-- date_add</span><br><span class="line">select date_add(now(), interval 70 day );</span><br><span class="line">select date_add(now(), interval 70 month );</span><br><span class="line"></span><br><span class="line">select datediff(&#x27;2021-12-01&#x27;,&#x27;2021-11-01&#x27;);</span><br><span class="line"></span><br><span class="line">-- 根据员工入职天数倒序排序</span><br><span class="line">select name,datediff(curdate(), entrydate) &#x27;entrydays&#x27; from emp order by entrydays desc ;</span><br><span class="line"></span><br><span class="line">-- 流程函数</span><br><span class="line">select if(true, &#x27;OK&#x27;, &#x27;Error&#x27;);</span><br><span class="line"></span><br><span class="line">select ifnull(null,&#x27;Default&#x27;)</span><br><span class="line"></span><br><span class="line">-- case when then else and</span><br><span class="line">-- 需求：查询emp表的员工姓名和工作地址(北京/上海 ---&gt; 一线城市，其他 ---&gt; 二线城市)</span><br><span class="line">select</span><br><span class="line">    name,</span><br><span class="line">    (case workaddress when &#x27;北京&#x27; then &#x27;一线城市&#x27; when &#x27;上海&#x27; then &#x27;一线城市&#x27; else &#x27;二线城市&#x27; end) &#x27;工作地址&#x27;</span><br><span class="line">from emp;</span><br><span class="line"></span><br><span class="line">-- 统计班级各个学员的成绩，展示规则如下</span><br><span class="line">-- &gt;= 85,展示优秀</span><br><span class="line">-- &gt;= 60，展示及格</span><br><span class="line">-- 否则，展示不及格</span><br><span class="line">create table score(</span><br><span class="line">    id int comment &#x27;ID&#x27;,</span><br><span class="line">    name varchar(20) comment &#x27;姓名&#x27;,</span><br><span class="line">    math int comment &#x27;数学&#x27;,</span><br><span class="line">    english int comment &#x27;英语&#x27;,</span><br><span class="line">    chinese int comment &#x27;语文&#x27;</span><br><span class="line">) comment &#x27;学员成绩表&#x27;;</span><br><span class="line">insert into score(id, name, math, english, chinese)</span><br><span class="line">values (1,&#x27;Tom&#x27;, 67, 88, 95),</span><br><span class="line">       (2,&#x27;Rose&#x27;, 23, 66, 90),</span><br><span class="line">       (3,&#x27;Jack&#x27;, 56, 98, 76);</span><br><span class="line"></span><br><span class="line">select</span><br><span class="line">    id,</span><br><span class="line">    name,</span><br><span class="line">    case when math &gt;= 85 then &#x27;优秀&#x27; when math &gt;= 60 then &#x27;及格&#x27; else &#x27;不及格&#x27; end &#x27;数学&#x27;,</span><br><span class="line">    case when english &gt;= 85 then &#x27;优秀&#x27; when math &gt;= 60 then &#x27;及格&#x27; else &#x27;不及格&#x27; end &#x27;英语&#x27;,</span><br><span class="line">    case when chinese &gt;= 85 then &#x27;优秀&#x27; when math &gt;= 60 then &#x27;及格&#x27; else &#x27;不及格&#x27; end &#x27;语文&#x27;</span><br><span class="line">from score;</span><br></pre></td></tr></table></figure>

<h2 id="约束"><a href="#约束" class="headerlink" title="约束"></a>约束</h2><ol>
<li><p>非空约束：NOT NULL</p>
</li>
<li><p>唯一约束：UNIQUE</p>
</li>
<li><p>主键约束：PRIMARY KEY（自增：AUTO_INCREMENT）</p>
</li>
<li><p>默认约束：DEFAULT</p>
</li>
<li><p>检查约束：CHECK</p>
</li>
<li><p>外键约束：FOREIGN KEY</p>
</li>
</ol>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">create table user(</span><br><span class="line">    id int primary key auto_increment comment  &#x27;ID&#x27;,</span><br><span class="line">    name varchar(10) NOT NULL unique comment &#x27;姓名&#x27;,</span><br><span class="line">    age int check ( age &gt; 0 &amp;&amp; age &lt;= 120 ) comment &#x27;年龄&#x27;,</span><br><span class="line">    status char(1) default &#x27;1&#x27; comment &#x27;状态&#x27;,</span><br><span class="line">    geder char(1) comment &#x27;性别&#x27;</span><br><span class="line">)comment &#x27;用户&#x27;;</span><br><span class="line"></span><br><span class="line">insert into user (name, age, status, geder) values (&#x27;Tom1&#x27;,18,&#x27;1&#x27;,&#x27;男&#x27;), (&#x27;Tom2&#x27;,28,&#x27;0&#x27;,&#x27;男&#x27;);</span><br><span class="line">insert into user (name, age, status, geder) values (&#x27;Tom3&#x27;,38,&#x27;1&#x27;,&#x27;男&#x27;);</span><br></pre></td></tr></table></figure>

<h3 id="约束-外键"><a href="#约束-外键" class="headerlink" title="约束-外键"></a>约束-外键</h3><p>外键用来让两张表的数据之间建立连接，从而<strong>保证数据的一致性和完整性</strong>。</p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">create table dept(</span><br><span class="line">    id int auto_increment comment  &#x27;ID&#x27; primary key ,</span><br><span class="line">    name varchar(50) not null comment &#x27;部门名称&#x27;</span><br><span class="line">)comment &#x27;部门表&#x27;;</span><br><span class="line"></span><br><span class="line">insert into dept(id, name) values (1, &#x27;研发部&#x27;),(2, &#x27;市场部&#x27;),(3, &#x27;财务部&#x27;),(4, &#x27;销售部&#x27;),(5, &#x27;总经办&#x27;);</span><br><span class="line"></span><br><span class="line">create table emp_new(</span><br><span class="line">    id int auto_increment comment &#x27;ID&#x27; primary key ,</span><br><span class="line">    name varchar(50) not null comment &#x27;姓名&#x27;,</span><br><span class="line">    age int comment &#x27;年龄&#x27;,</span><br><span class="line">    job varchar(20) comment &#x27;职务&#x27;,</span><br><span class="line">    salary int comment &#x27;薪资&#x27;,</span><br><span class="line">    entrydate date comment &#x27;入职时间&#x27;,</span><br><span class="line">    managerid int comment &#x27;直属领导ID&#x27;,</span><br><span class="line">    dept_id int comment &#x27;部门ID&#x27;</span><br><span class="line">)comment &#x27;员工表&#x27;;</span><br><span class="line"></span><br><span class="line">insert into emp_new (id, name, age, job, salary, entrydate, managerid, dept_id)</span><br><span class="line">values  (1, &#x27;金庸&#x27;, 66, &#x27;总裁&#x27;, 20000, &#x27;2000-01-01&#x27;,null,5),</span><br><span class="line">        (2, &#x27;张无忌&#x27;, 20, &#x27;项目经理&#x27;, 12500, &#x27;2005-12-05&#x27;,1,1),</span><br><span class="line">        (3, &#x27;杨逍&#x27;, 33, &#x27;开发&#x27;, 8400, &#x27;2000-11-03&#x27;,2,1),</span><br><span class="line">        (4, &#x27;韦一笑&#x27;, 48, &#x27;开发&#x27;, 11000, &#x27;2002-02-05&#x27;,2,1),</span><br><span class="line">        (5, &#x27;常遇春&#x27;, 43, &#x27;开发&#x27;, 10500, &#x27;2004-09-07&#x27;,3,1),</span><br><span class="line">        (6, &#x27;小昭&#x27;, 19, &#x27;程序员鼓励师&#x27;, 6600, &#x27;2004-10-12&#x27;,2,1);</span><br><span class="line"></span><br><span class="line">-- 添加外键</span><br><span class="line">alter table emp_new add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);</span><br><span class="line"></span><br><span class="line">-- 删除外键</span><br><span class="line">alter table emp_new drop foreign key fk_emp_dept_id;</span><br><span class="line"></span><br><span class="line">-- 外键的删除和更新行为</span><br><span class="line">alter table emp_new add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;</span><br><span class="line"></span><br><span class="line">alter table emp_new add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null ;</span><br></pre></td></tr></table></figure>

<h2 id="多表关系"><a href="#多表关系" class="headerlink" title="多表关系"></a>多表关系</h2><p>一对多（多对一）</p>
<p>案例：部门与员工的关系</p>
<p>实现：<strong>在多的一方建立外键，指向一的一方的主键</strong></p>
<p>多对多</p>
<p>案例：学生与课程的关系</p>
<p>实现：<strong>建立第三张中间表，中间表至少包含两个外键，分别关联两方主键</strong></p>
<p>一对一</p>
<p>案例：用户与用户详情的关系</p>
<p>关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率</p>
<p>实现：<strong>在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（unique）</strong></p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">-- 多对多</span><br><span class="line">create table student(</span><br><span class="line">    id int auto_increment primary key comment &#x27;主键ID&#x27;,</span><br><span class="line">    name varchar(10) comment &#x27;姓名&#x27;,</span><br><span class="line">    no varchar(10) comment &#x27;学号&#x27;</span><br><span class="line">)comment &#x27;学生表&#x27;;</span><br><span class="line">insert into student values</span><br><span class="line">                        (null,&#x27;黛绮丝&#x27;,&#x27;200100101&#x27;),</span><br><span class="line">                        (null,&#x27;谢逊&#x27;,&#x27;200100102&#x27;),</span><br><span class="line">                        (null,&#x27;殷天正&#x27;,&#x27;200100103&#x27;),</span><br><span class="line">                        (null,&#x27;韦一笑&#x27;,&#x27;200100104&#x27;);</span><br><span class="line"></span><br><span class="line">create table course(</span><br><span class="line">    id int auto_increment primary key comment &#x27;主键ID&#x27;,</span><br><span class="line">    name varchar(10) comment &#x27;课程名称&#x27;</span><br><span class="line">)comment &#x27;课程表&#x27;;</span><br><span class="line">insert into course values (null,&#x27;Java&#x27;),(null,&#x27;Php&#x27;),(null,&#x27;Mysql&#x27;),(null,&#x27;Hadoop&#x27;);</span><br><span class="line"></span><br><span class="line">create table student_course(</span><br><span class="line">    id int auto_increment primary key comment &#x27;主键ID&#x27;,</span><br><span class="line">    studentid int not null comment &#x27;学生ID&#x27;,</span><br><span class="line">    courseid int not null comment &#x27;课程ID&#x27;,</span><br><span class="line">    constraint fk_courseid foreign key (courseid) references course(id),</span><br><span class="line">    constraint fk_studentid foreign key (studentid) references student(id)</span><br><span class="line">)comment &#x27;学生课程中间表&#x27;;</span><br><span class="line"></span><br><span class="line">insert into student_course values (NULL,1,1),(NULL,1,2),(NULL,1,3),(NULL,2,2),(NULL,2,3),(NULL,3,4);</span><br><span class="line"></span><br><span class="line">create table tb_user(</span><br><span class="line">    id int auto_increment primary key comment &#x27;主键ID&#x27;,</span><br><span class="line">    name varchar(10) comment &#x27;姓名&#x27;,</span><br><span class="line">    age int comment &#x27;年龄&#x27;,</span><br><span class="line">    gender char(1) comment &#x27;1: 男 , 2: 女&#x27;,</span><br><span class="line">    phone char(11) comment &#x27;手机号&#x27;</span><br><span class="line">) comment &#x27;用户基本信息表&#x27;;</span><br><span class="line"></span><br><span class="line">create table tb_user_edu(</span><br><span class="line">    id int auto_increment primary key comment &#x27;主键ID&#x27;,</span><br><span class="line">    degree varchar(20) comment &#x27;学历&#x27;,</span><br><span class="line">    major varchar(20) comment &#x27;专业&#x27;,</span><br><span class="line">    primaryschool varchar(50) comment &#x27;小学&#x27;,</span><br><span class="line">    middleschool varchar(50) comment &#x27;中学&#x27;,</span><br><span class="line">    university varchar(50) comment &#x27;大学&#x27;,</span><br><span class="line">    userid int unique comment &#x27;用户ID&#x27;,</span><br><span class="line">    constraint fk_userid foreign key (userid) references tb_user(id)</span><br><span class="line">)comment &#x27;用户教育信息表&#x27;;</span><br><span class="line"></span><br><span class="line">insert into tb_user(id, name, age, gender, phone) values</span><br><span class="line">    (null,&#x27;黄渤&#x27;, 45, &#x27;1&#x27;, &#x27;18800001111&#x27;),</span><br><span class="line">    (null,&#x27;冰冰&#x27;, 35, &#x27;2&#x27;, &#x27;18800002222&#x27;),</span><br><span class="line">    (null,&#x27;马云&#x27;, 55, &#x27;1&#x27;, &#x27;18800003333&#x27;),</span><br><span class="line">    (null,&#x27;李彦宏&#x27;, 50, &#x27;1&#x27;, &#x27;18800004444&#x27;);</span><br><span class="line"></span><br><span class="line">insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) values</span><br><span class="line">    (null,&#x27;本科&#x27;, &#x27;舞蹈&#x27;, &#x27;静安区第一小学&#x27;, &#x27;静安区第一中学&#x27;,&#x27;北京舞蹈学院&#x27;,1),</span><br><span class="line">    (null,&#x27;硕士&#x27;, &#x27;表演&#x27;, &#x27;朝阳区第一小学&#x27;, &#x27;朝阳区第一中学&#x27;,&#x27;北京电影学院&#x27;,2),</span><br><span class="line">    (null,&#x27;本科&#x27;, &#x27;英语&#x27;, &#x27;杭州市第一小学&#x27;, &#x27;杭州市第一中学&#x27;,&#x27;杭州师范大学&#x27;,3),</span><br><span class="line">    (null,&#x27;本科&#x27;, &#x27;应用数学&#x27;, &#x27;阳泉第一小学&#x27;, &#x27;阳泉第一中学&#x27;,&#x27;清华大学&#x27;,4);</span><br></pre></td></tr></table></figure>

<h3 id="多表查询"><a href="#多表查询" class="headerlink" title="多表查询"></a>多表查询</h3><p>消除笛卡尔积</p>
<p>分类：连接查询：内连接 外连接 自连接</p>
<p>​        自连接必须使用表别名</p>
<p>​   子查询（嵌套查询）</p>
<p>​   子查询外部的语句可以是<strong>INSERT&#x2F;UPDATE&#x2F;DELETE&#x2F;SELECT</strong>任何一个</p>
<p>根据子查询结果不同，分为：</p>
<ul>
<li>标量子查询（子查询结果为单个值（数字、字符串、日期等））</li>
<li>列子查询（子查询结果为一列（可以是多行））常用的操作符 IN、NOT IN、ANY、SOME、ALL</li>
<li>行子查询（子查询结果为一行）</li>
<li>表子查询（子查询结果为多行多列）</li>
</ul>
<p>根据子查询位置，分为WHERE之后，FROM之后，SELECT之后</p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">-- 多表查询</span><br><span class="line">select * from employee,dept where employee.dept_id = dept.id;</span><br><span class="line"></span><br><span class="line">-- 内连接</span><br><span class="line">-- 1.查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）</span><br><span class="line">-- 表结构：emp , dept</span><br><span class="line">-- 连接条件 emp.dept_ip = dept.id</span><br><span class="line"></span><br><span class="line">select employee.name,dept.name from employee ,dept where employee.dept_id = dept.id;</span><br><span class="line">-- 别名</span><br><span class="line">select e.name,d.name from employee e, dept d where e.dept_id = d.id;</span><br><span class="line"></span><br><span class="line">-- 2.查询每一个员工的姓名，及关联的部门的名称（显式内连接实现） -- inner join ...on...</span><br><span class="line">-- 表结构：emp , dept</span><br><span class="line">-- 连接条件 emp.dept_ip = dept.id</span><br><span class="line"></span><br><span class="line">select e.name,d.name from employee e inner join dept d on e.dept_id = d.id;</span><br><span class="line"></span><br><span class="line">select e.name,d.name from employee e join dept d on e.dept_id = d.id;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">-- 外连接</span><br><span class="line">-- 1.查询emp表的所有数据，和对应的部门信息（左外连接）</span><br><span class="line">-- 表结构：emp , dept</span><br><span class="line">-- 连接条件 emp.dept_ip = dept.id</span><br><span class="line"></span><br><span class="line">select e.*,d.name from employee e left outer join dept d on e.dept_id = d.id;</span><br><span class="line"></span><br><span class="line">select e.*,d.name from employee e left join dept d on e.dept_id = d.id;</span><br><span class="line"></span><br><span class="line">-- 2.查询dept表的所有数据，和对应的员工信息（右外连接）</span><br><span class="line">-- 表结构：emp , dept</span><br><span class="line">-- 连接条件 emp.dept_ip = dept.id</span><br><span class="line">select e.*,d.* from employee e right outer join dept d on e.dept_id = d.id;</span><br><span class="line"></span><br><span class="line">select e.*,d.* from employee e right join dept d on e.dept_id = d.id;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">-- 自连接</span><br><span class="line">-- 1.查询员工及其所属领导的名字</span><br><span class="line">-- 表结构 emp</span><br><span class="line"></span><br><span class="line">select a.name,b.name from employee a, employee b where a.managerid = b.id;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">-- 2.查询所有员工emp 及其领导的名字emp，如果员工没有领导，也需要查询出来</span><br><span class="line">-- 表结构 emp a, emp b</span><br><span class="line">select a.name &#x27;员工&#x27;,b.name &#x27;领导&#x27; from employee a left join emp b on a.managerid = b.id;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">-- union all ,union</span><br><span class="line">-- 1。将薪资低于5000的员工和年龄大于50岁的员工全部查询出来。</span><br><span class="line"></span><br><span class="line">select * from employee where age &gt; 50</span><br><span class="line">union all -- 直接将查询结果合并</span><br><span class="line">select * from employee where managerid = 1;</span><br><span class="line"></span><br><span class="line">select * from employee where age &gt; 50</span><br><span class="line">union   -- 查询后结果去重后合并</span><br><span class="line">select * from employee where managerid = 1;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">-- 标量子查询</span><br><span class="line">-- 1.查询&#x27;销售部&#x27;的所有员工信息</span><br><span class="line">-- a、查询&#x27;销售部&#x27;部门id</span><br><span class="line">select d.id from dept d where name=&#x27;销售部&#x27;;</span><br><span class="line"></span><br><span class="line">-- b、根据销售部门id，查询员工信息</span><br><span class="line">select * from employee where dept_id = 4;</span><br><span class="line"></span><br><span class="line">select * from employee where dept_id = (select d.id from dept d where name=&#x27;销售部&#x27;);</span><br><span class="line"></span><br><span class="line">-- 2.查询在&#x27;方东白&#x27;入职之后的员工信息</span><br><span class="line">select * from employee e where e.entrydate &gt; (select e.entrydate from employee e where e.name=&#x27;方东白&#x27;);</span><br><span class="line"></span><br><span class="line">-- 列子查询</span><br><span class="line">select id from dept where name = &#x27;市场部&#x27; or name = &#x27;销售部&#x27;;</span><br><span class="line"></span><br><span class="line">select * from employee where dept_id in (2,4);</span><br><span class="line"></span><br><span class="line">select * from employee where dept_id in (select id from dept where name = &#x27;市场部&#x27; or name = &#x27;销售部&#x27;);</span><br><span class="line">-- all any some</span><br><span class="line"></span><br><span class="line">-- 行子查询</span><br><span class="line">select * from employee where (dept_id,managerid) = (select dept_id,managerid from employee where name = &#x27;张无忌&#x27;);</span><br><span class="line"></span><br><span class="line">-- 表子查询</span><br><span class="line">select * from employee where entrydate &gt; &#x27;2006-01-01&#x27;;</span><br><span class="line"></span><br><span class="line">select e.*,d.* from (select * from employee where entrydate &gt; &#x27;2006-01-01&#x27;) e left join dept d on e.dept_id = d.id;</span><br></pre></td></tr></table></figure>

<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">create table salgrade(</span><br><span class="line">    grade int,</span><br><span class="line">    losal int,</span><br><span class="line">    hisal int</span><br><span class="line">)comment &#x27;薪资登记表&#x27;;</span><br><span class="line"></span><br><span class="line">insert into salgrade values (1,0,3000);</span><br><span class="line">insert into salgrade values (2,3001,5000);</span><br><span class="line">insert into salgrade values (3,5001,8000);</span><br><span class="line">insert into salgrade values (4,8001,10000);</span><br><span class="line">insert into salgrade values (5,10001,15000);</span><br><span class="line">insert into salgrade values (6,15001,20000);</span><br><span class="line">insert into salgrade values (7,20001,25000);</span><br><span class="line">insert into salgrade values (8,25001,30000);</span><br></pre></td></tr></table></figure>

<h2 id="事务"><a href="#事务" class="headerlink" title="事务"></a>事务</h2><figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">create table account(</span><br><span class="line">    id int auto_increment primary key comment &#x27;主键ID&#x27;,</span><br><span class="line">    name varchar(10) comment &#x27;姓名&#x27;,</span><br><span class="line">    money int comment &#x27;余额&#x27;</span><br><span class="line">)comment &#x27;账户表&#x27;;</span><br><span class="line">insert into account (id, name, money) VALUES (null,&#x27;张三&#x27;,2000),(null,&#x27;李四&#x27;,2000);</span><br><span class="line"></span><br><span class="line">-- 恢复数据</span><br><span class="line">update account set money = 2000 where name = &#x27;张三&#x27; or name = &#x27;李四&#x27;;</span><br><span class="line"></span><br><span class="line">select @@autocommit;</span><br><span class="line">set autocommit = 1;</span><br><span class="line"></span><br><span class="line">-- 转账操作</span><br><span class="line">select money from account where name = &#x27;张三&#x27;;</span><br><span class="line"></span><br><span class="line">update account set money = money - 1000 where name = &#x27;张三&#x27;;</span><br><span class="line"></span><br><span class="line">update account set money = money + 1000 where name = &#x27;李四&#x27;;</span><br><span class="line"></span><br><span class="line">-- 提交事务</span><br><span class="line">commit;</span><br><span class="line">-- 回滚事务</span><br><span class="line">rollback ;</span><br><span class="line"></span><br><span class="line">-- 方式二</span><br><span class="line">start transaction ;</span><br><span class="line"></span><br><span class="line">-- 转账操作</span><br><span class="line">select money from account where name = &#x27;张三&#x27;;</span><br><span class="line"></span><br><span class="line">update account set money = money - 1000 where name = &#x27;张三&#x27;;</span><br><span class="line"></span><br><span class="line">update account set money = money + 1000 where name = &#x27;李四&#x27;;</span><br><span class="line"></span><br><span class="line">commit ;</span><br><span class="line"></span><br><span class="line">rollback ;</span><br></pre></td></tr></table></figure>

<h3 id="事务四大特性（ACID）"><a href="#事务四大特性（ACID）" class="headerlink" title="事务四大特性（ACID）"></a>事务四大特性（ACID）</h3><p><strong>原子性</strong>（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。</p>
<p><strong>一致性</strong>（Consistency）：事务完成时，必须使所有的数据都保持一致状态。</p>
<p><strong>隔离性</strong>（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。</p>
<p><strong>持久性</strong>（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。</p>
<p>并发事务问题</p>
<p>脏读：一个事务读到另外一个事务还没有提交的数据</p>
<p>不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同</p>
<p>幻读：一个事务按照条件查询数据时，没有对应的数据行，但在插入数据时，这行数据已经存在</p>
<h2 id="mysql体系结构"><a href="#mysql体系结构" class="headerlink" title="mysql体系结构"></a>mysql体系结构</h2><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427180133731.png" alt="image-20230427180133731"></p>
<h2 id="存储引擎"><a href="#存储引擎" class="headerlink" title="存储引擎"></a>存储引擎</h2><p>存储引擎就是存储数据、建立索引、更新&#x2F;查询数据等技术的实现方式。<strong>存储引擎是基于表的</strong>，而不是基于库的，所以存储引擎也可被称为表类型。</p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">show engines;</span><br><span class="line"></span><br><span class="line">create table table_name(</span><br><span class="line"> ......</span><br><span class="line">) engine=MyISAM;</span><br></pre></td></tr></table></figure>

<h3 id="InnoDB"><a href="#InnoDB" class="headerlink" title="InnoDB"></a>InnoDB</h3><p>InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎。</p>
<p>特点：</p>
<ul>
<li>DML操作遵循ACID模型，支持事务；</li>
<li>行级锁，提高并发访问性能；</li>
<li>支持外键FOREIGN KEY约束，保证数据的完整性和正确性。</li>
</ul>
<p>文件</p>
<p>xxx.ibd：xxx代表表名，InnoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。</p>
<p>参数：innodb_file_per_table</p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427183014958.png" alt="image-20230427183014958"></p>
<h3 id="MyISAM"><a href="#MyISAM" class="headerlink" title="MyISAM"></a>MyISAM</h3><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427183159620.png" alt="image-20230427183159620"></p>
<h3 id="Memory"><a href="#Memory" class="headerlink" title="Memory"></a>Memory</h3><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427183252841.png" alt="image-20230427183252841"></p>
<h3 id="面试常问"><a href="#面试常问" class="headerlink" title="面试常问"></a>面试常问</h3><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427183320836.png" alt="image-20230427183320836"></p>
<p>InnoDB的三大特点：<strong>事务、行级锁、支持外键</strong></p>
<h3 id="存储引擎选择"><a href="#存储引擎选择" class="headerlink" title="存储引擎选择"></a>存储引擎选择</h3><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427183605101.png" alt="image-20230427183605101"></p>
<p>MyISAM –&gt; MongoDB</p>
<p>Memory –&gt; Redis</p>
<h2 id="索引"><a href="#索引" class="headerlink" title="索引"></a>索引</h2><p>mysql on linux</p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">create user &#x27;root&#x27;@&#x27;%&#x27; identified with mysql_native_password by &#x27;123&#x27;;</span><br><span class="line"></span><br><span class="line">grant all on *.* to &#x27;frode&#x27;@&#x27;%&#x27;;</span><br></pre></td></tr></table></figure>

<h3 id="索引概述"><a href="#索引概述" class="headerlink" title="索引概述"></a>索引概述</h3><p>帮助MySQL高效获取数据的有序数据结构</p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427185955088.png" alt="image-20230427185955088"></p>
<h3 id="索引结构"><a href="#索引结构" class="headerlink" title="索引结构"></a>索引结构</h3><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427190116510.png" alt="image-20230427190116510"></p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427190132895.png" alt="image-20230427190132895"></p>
<h4 id="二叉树"><a href="#二叉树" class="headerlink" title="二叉树"></a>二叉树</h4><p>缺点：顺序插入时，会形成一个链表。大数据量情况下，层级较深，检索速度慢。</p>
<p>红黑树解决树的平衡问题</p>
<p>红黑树缺点：大数据量情况下，层级较深，检索速度慢。</p>
<p>B-Tree（多路平衡查找树）</p>
<h4 id="B-Tree"><a href="#B-Tree" class="headerlink" title="B+Tree"></a>B+Tree</h4><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427200511891.png" alt="image-20230427200511891"></p>
<p>相对于B-Tree区别：</p>
<ol>
<li>所有的数据都会出现在叶子节点</li>
<li>叶子节点形成一个单向链表</li>
</ol>
<p><strong><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427200730369.png" alt="image-20230427200730369"></strong></p>
<h4 id="hash"><a href="#hash" class="headerlink" title="hash"></a>hash</h4><p>哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。</p>
<p>如果两个（或多个）键值，映射到一个相同的槽位上，就产生了hash冲突，可以通过链表来解决。</p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427201240516.png" alt="image-20230427201240516"></p>
<h4 id="Hash索引特点"><a href="#Hash索引特点" class="headerlink" title="Hash索引特点"></a>Hash索引特点</h4><ol>
<li>Hash索引只能用于对等比较（&#x3D;，in），不支持范围查询（between，&gt;，&lt;，…）</li>
<li>无法利用索引完成排序操作</li>
<li>查询效率高，通常只需要一次检索就可以，效率通常要高于B+Tree索引</li>
</ol>
<h4 id="为什么InnoDB存储引擎选择使用B-Tree索引结构？"><a href="#为什么InnoDB存储引擎选择使用B-Tree索引结构？" class="headerlink" title="为什么InnoDB存储引擎选择使用B+Tree索引结构？"></a>为什么InnoDB存储引擎选择使用B+Tree索引结构？</h4><ul>
<li>相对于二叉树，层级更少，搜索效率高；</li>
<li>对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；</li>
<li>相对于Hash索引，B+Tree支持范围匹配及排序操作；</li>
</ul>
<h3 id="索引分类"><a href="#索引分类" class="headerlink" title="索引分类"></a>索引分类</h3><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427202051248.png" alt="image-20230427202051248"></p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427202222359.png" alt="image-20230427202222359"></p>
<p>聚集索引一定存在</p>
<p>聚集索引的叶子节点存储这一行的数据</p>
<p>二级索引的叶子节点存储id</p>
<p><strong>回表查询：先走二级索引找到对应的主键值，然后根据主键值通过聚集索引找到这一行的行数据</strong></p>
<h3 id="索引语法"><a href="#索引语法" class="headerlink" title="索引语法"></a>索引语法</h3><figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">create index idx_user_email on tb_user(email);</span><br><span class="line"></span><br><span class="line">create unique index idx_user_email on tb_user(email);</span><br><span class="line"></span><br><span class="line"># 创建联合索引</span><br><span class="line">create index idx_user_pro_age_sta on tb_user(profession,age,status);</span><br><span class="line"></span><br><span class="line">drop index idx_name on tb_name;</span><br><span class="line"></span><br><span class="line">show index from tb_name;</span><br></pre></td></tr></table></figure>

<h3 id="SQL性能分析"><a href="#SQL性能分析" class="headerlink" title="SQL性能分析"></a>SQL性能分析</h3><ul>
<li>SQL执行频率</li>
</ul>
<p>查看当前数据库INSERT、UPDATE、DELETE、SELECT的访问频次</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">show</span> <span class="keyword">global</span> status <span class="keyword">like</span> <span class="string">&#x27;Com_______&#x27;</span>;</span><br></pre></td></tr></table></figure>

<ul>
<li>慢查询日志</li>
</ul>
<p>慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">show</span> variables <span class="keyword">like</span> <span class="string">&#x27;slow_query_log&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p>MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（&#x2F;etc&#x2F;my.cnf）中配置如下信息：</p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line"># 开启MySQL慢日志查询开关</span><br><span class="line">slow_query_log=1</span><br><span class="line"># 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志</span><br><span class="line">long_query_time=2</span><br></pre></td></tr></table></figure>

<ul>
<li>profile详情</li>
</ul>
<p>show profiles能够在做SQL优化时帮助我们了解时间耗费到了哪里。通过have_profiling参数，能够看到当前MySQL是否支持profile操作</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span> @<span class="variable">@profiling</span>;</span><br></pre></td></tr></table></figure>

<p>默认profiling是关闭的，可以通过set语句在session&#x2F;global级别开启</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">set</span> profiling<span class="operator">=</span><span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<p>查看指令的执行耗时</p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line"># 查看每一条SQL的耗时基本情况</span><br><span class="line">show profiles;</span><br><span class="line"></span><br><span class="line"># 查看指定query_id的SQL语句各个阶段的耗时情况</span><br><span class="line">show profile for query query_id;</span><br><span class="line"></span><br><span class="line"># 查看指定query_id的SQL语句CPU的使用情况</span><br><span class="line">show profile cpu for query query_id;</span><br></pre></td></tr></table></figure>

<ul>
<li>explain执行计划</li>
</ul>
<p><strong>explain</strong>或者<strong>desc</strong>命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序</p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line"># 直接在select语句之前加上关键字explain/desc</span><br><span class="line">explain select 字段列表 from 表名 where 条件;</span><br></pre></td></tr></table></figure>

<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427213022244.png" alt="image-20230427213022244"></p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427213234944.png" alt="image-20230427213234944"></p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427213156104.png" alt="image-20230427213156104"></p>
<h3 id="索引使用"><a href="#索引使用" class="headerlink" title="索引使用"></a>索引使用</h3><h4 id="索引失效"><a href="#索引失效" class="headerlink" title="索引失效"></a>索引失效</h4><ul>
<li><h5 id="最左前缀法则"><a href="#最左前缀法则" class="headerlink" title="最左前缀法则"></a>最左前缀法则</h5></li>
</ul>
<p>如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。</p>
<p>如果跳跃某一列，**<font color='cpink'>索引将部分失效（后面的字段索引失效）。</font>**</p>
<ul>
<li><h5 id="范围查询"><a href="#范围查询" class="headerlink" title="范围查询"></a>范围查询</h5></li>
</ul>
<p>联合索引中，出现范围查询（&gt;，&lt;），**<font color='cpink'>范围查询右侧的列索引失效。</font>**</p>
<p>解决办法： &gt;&#x3D;</p>
<ul>
<li><h5 id="索引列运算"><a href="#索引列运算" class="headerlink" title="索引列运算"></a>索引列运算</h5></li>
</ul>
<p>不要在索引列上进行运算操作，**<font color='cpink'>索引将失效。</font>**</p>
<ul>
<li><h5 id="字符串不加引号"><a href="#字符串不加引号" class="headerlink" title="字符串不加引号"></a>字符串不加引号</h5></li>
</ul>
<p>字符串类型字段使用时，不加引号，**<font color='cpink'>索引将失效。</font>**</p>
<ul>
<li><h5 id="模糊查询"><a href="#模糊查询" class="headerlink" title="模糊查询"></a>模糊查询</h5></li>
</ul>
<p>如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。        ‘%工程’</p>
<ul>
<li><h5 id="or连接的条件"><a href="#or连接的条件" class="headerlink" title="or连接的条件"></a>or连接的条件</h5></li>
</ul>
<p>用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。</p>
<ul>
<li><h5 id="数据分布影响"><a href="#数据分布影响" class="headerlink" title="数据分布影响"></a>数据分布影响</h5></li>
</ul>
<p>如果MySQL评估使用索引比全表更慢，则不使用索引</p>
<h4 id="SQL提示"><a href="#SQL提示" class="headerlink" title="SQL提示"></a>SQL提示</h4><p>是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line">explain <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> tb_user use index(idx_name) <span class="keyword">where</span> conditon</span><br><span class="line"></span><br><span class="line">explain <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> tb_user ignore index(idx_name) <span class="keyword">where</span> conditon</span><br><span class="line"></span><br><span class="line">explain <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> tb_user force index(idx_name) <span class="keyword">where</span> conditon</span><br></pre></td></tr></table></figure>

<h4 id="覆盖索引"><a href="#覆盖索引" class="headerlink" title="覆盖索引"></a>覆盖索引</h4><p>尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *</p>
<blockquote>
<p>using index condition：查找使用了索引，但是需要回表查询数据</p>
<p>using where, using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据</p>
</blockquote>
<h4 id="前缀索引"><a href="#前缀索引" class="headerlink" title="前缀索引"></a>前缀索引</h4><p>当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时浪费大量的磁盘IO，影响查询效率，此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率。</p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427221554125.png" alt="image-20230427221554125"></p>
<h4 id="单列索引与联合索引"><a href="#单列索引与联合索引" class="headerlink" title="单列索引与联合索引"></a>单列索引与联合索引</h4><p>单列索引：即一个索引只包含单个列</p>
<p>联合索引：即一个索引包含了多个列</p>
<p>在业务场景中，如果<strong>存在多个查询条件</strong>，考虑针对于查询字段建立索引时，建议建立<strong>联合索引</strong>（考虑顺序），而非单列索引</p>
<p>多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询</p>
<h3 id="索引设计原则"><a href="#索引设计原则" class="headerlink" title="索引设计原则"></a>索引设计原则</h3><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230427222932434.png" alt="image-20230427222932434"></p>
<h2 id="SQL优化"><a href="#SQL优化" class="headerlink" title="SQL优化"></a>SQL优化</h2><h3 id="插入数据"><a href="#插入数据" class="headerlink" title="插入数据"></a>插入数据</h3><h4 id="Insert优化"><a href="#Insert优化" class="headerlink" title="Insert优化"></a>Insert优化</h4><h5 id="批量插入"><a href="#批量插入" class="headerlink" title="批量插入"></a>批量插入</h5><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_test <span class="keyword">values</span>(<span class="number">1</span>,<span class="string">&#x27;Tom&#x27;</span>),(<span class="number">2</span>,<span class="string">&#x27;cat&#x27;</span>),(<span class="number">3</span>,<span class="string">&#x27;jerry&#x27;</span>);</span><br></pre></td></tr></table></figure>

<h5 id="手动提交事务"><a href="#手动提交事务" class="headerlink" title="手动提交事务"></a>手动提交事务</h5><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">start</span> transaction;</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_test <span class="keyword">values</span>(<span class="number">1</span>,<span class="string">&#x27;Tom&#x27;</span>),(<span class="number">2</span>,<span class="string">&#x27;cat&#x27;</span>),(<span class="number">3</span>,<span class="string">&#x27;jerry&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_test <span class="keyword">values</span>(<span class="number">1</span>,<span class="string">&#x27;Tom&#x27;</span>),(<span class="number">2</span>,<span class="string">&#x27;cat&#x27;</span>),(<span class="number">3</span>,<span class="string">&#x27;jerry&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_test <span class="keyword">values</span>(<span class="number">1</span>,<span class="string">&#x27;Tom&#x27;</span>),(<span class="number">2</span>,<span class="string">&#x27;cat&#x27;</span>),(<span class="number">3</span>,<span class="string">&#x27;jerry&#x27;</span>);</span><br><span class="line"><span class="keyword">commit</span>;</span><br></pre></td></tr></table></figure>

<h5 id="主键顺序插入"><a href="#主键顺序插入" class="headerlink" title="主键顺序插入"></a>主键顺序插入</h5><figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">主键乱序插入： 8 1 9 21 88 2 4 15 ...</span><br><span class="line">主键顺序插入： 1 2 3 4 5 6 7 8 9 ...</span><br></pre></td></tr></table></figure>

<p>主键顺序插入性能高于乱序插入</p>
<h5 id="大批量插入数据"><a href="#大批量插入数据" class="headerlink" title="大批量插入数据"></a>大批量插入数据</h5><p>如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 客户端连接服务端时， 加上参数 <span class="comment">--local-infile</span></span><br><span class="line">mysql <span class="comment">--local-infile -u root -p</span></span><br><span class="line"># 设置全局参数<span class="keyword">local</span><span class="operator">-</span>infile为<span class="number">1</span>，开启从本地加载文件导入数据的开关</span><br><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> local_infile<span class="operator">=</span><span class="number">1</span>;</span><br><span class="line"># 执行load指令将准备好的数据，加载到表结构中</span><br><span class="line">load data <span class="keyword">local</span> infile <span class="string">&#x27;/root/sql1.log&#x27;</span> <span class="keyword">into</span> <span class="keyword">table</span> <span class="string">&#x27;tb_user&#x27;</span> fields terminated <span class="keyword">by</span> <span class="string">&#x27;,&#x27;</span> lines terminated <span class="keyword">by</span> <span class="string">&#x27;\n&#x27;</span>;</span><br></pre></td></tr></table></figure>

<h3 id="主键优化"><a href="#主键优化" class="headerlink" title="主键优化"></a>主键优化</h3><h4 id="数据组织方式"><a href="#数据组织方式" class="headerlink" title="数据组织方式"></a>数据组织方式</h4><p>在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为<font color='cpink'>索引组织表</font>（index organized table IOT）</p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428085525370.png" alt="image-20230428085525370"></p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428085754993.png" alt="image-20230428085754993"></p>
<h4 id="主键设计原则"><a href="#主键设计原则" class="headerlink" title="主键设计原则"></a>主键设计原则</h4><ul>
<li>满足业务需求的情况下，尽量降低主键的长度。</li>
<li>插入数据时，尽量选择顺序插入，选择使用auto_increment自增主键。</li>
<li>尽量不要使用UUID做主键或者是其他自然主键，如身份证号。</li>
<li>业务操作时，避免对主键的修改。</li>
</ul>
<h3 id="Order-by优化"><a href="#Order-by优化" class="headerlink" title="Order by优化"></a>Order by优化</h3><p><img src="C:\Users\Frode\AppData\Roaming\Typora\typora-user-images\image-20230428091429780.png" alt="image-20230428091429780"></p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428091503273.png" alt="image-20230428091503273"></p>
<ul>
<li>根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。</li>
<li>尽量使用覆盖索引</li>
<li>多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC&#x2F;DESC）</li>
<li>如果不可避免地出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认256k）</li>
</ul>
<h3 id="group-by优化"><a href="#group-by优化" class="headerlink" title="group by优化"></a>group by优化</h3><ul>
<li>在分组操作时，可以通过索引来提高效率</li>
<li>分组操作时，索引的使用也满足最左前缀法则</li>
</ul>
<h3 id="limit优化"><a href="#limit优化" class="headerlink" title="limit优化"></a>limit优化</h3><p>覆盖索引+子查询</p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428092757214.png" alt="image-20230428092757214"></p>
<h3 id="count优化"><a href="#count优化" class="headerlink" title="count优化"></a>count优化</h3><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428092919638.png" alt="image-20230428092919638"></p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428093310379.png" alt="image-20230428093310379"></p>
<h3 id="update优化"><a href="#update优化" class="headerlink" title="update优化"></a>update优化</h3><p>更新字段时一定要<strong>根据索引更新</strong>，如果字段没有索引会将整张表锁住</p>
<p>InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。</p>
<h2 id="视图-x2F-存储过程-x2F-触发器"><a href="#视图-x2F-存储过程-x2F-触发器" class="headerlink" title="视图&#x2F;存储过程&#x2F;触发器"></a>视图&#x2F;存储过程&#x2F;触发器</h2><h3 id="视图"><a href="#视图" class="headerlink" title="视图"></a>视图</h3><p>视图（view）是一种虚拟存在的表，视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。</p>
<p>视图只保存了查询的SQL逻辑，不保存查询结果。</p>
<ul>
<li>语法</li>
</ul>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="comment">-- 创建视图</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">or</span> replace <span class="keyword">view</span> stu_v_1 <span class="keyword">as</span> <span class="keyword">select</span> id,name <span class="keyword">from</span> student <span class="keyword">where</span> id <span class="operator">&lt;=</span> <span class="number">10</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询视图</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">create</span> <span class="keyword">view</span> stu_v_1;</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> stu_v_1;</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> stu_v_1 <span class="keyword">where</span> id <span class="operator">&lt;=</span><span class="number">3</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 修改视图</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">or</span> replace <span class="keyword">view</span> stu_v_1 <span class="keyword">as</span> <span class="keyword">select</span> id,name,<span class="keyword">no</span> <span class="keyword">from</span> student <span class="keyword">where</span> id <span class="operator">&lt;=</span> <span class="number">10</span>;</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">view</span> stu_v_1 <span class="keyword">as</span> <span class="keyword">select</span> id,name <span class="keyword">from</span> student <span class="keyword">where</span> id <span class="operator">&lt;=</span> <span class="number">10</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 删除视图</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">view</span> if <span class="keyword">exists</span> stu_v_1;</span><br></pre></td></tr></table></figure>

<ul>
<li>视图的检查选项</li>
</ul>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">or</span> replace <span class="keyword">view</span> stu_v_1 <span class="keyword">as</span> <span class="keyword">select</span> id,name <span class="keyword">from</span> student <span class="keyword">where</span> id <span class="operator">&lt;=</span> <span class="number">20</span> <span class="keyword">with</span> <span class="keyword">cascaded</span> <span class="keyword">check</span> option ;</span><br></pre></td></tr></table></figure>

<p>with cascaded&#x2F;local check option</p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428101819308.png" alt="image-20230428101819308"></p>
<p>如果视图使用 <code>WITH LOCAL CHECK OPTION</code>，MySQL将检查具有<code>WITH LOCAL CHECK OPTION</code>和<code>WITH CASCADED CHECK OPTION</code>的视图规则。</p>
<p>它与使用<code>WITH CASCADED CHECK OPTION</code>MySQL检查所有相关视图的规则的视图不同。</p>
<p>请注意，在MySQL 5.7.6之前，如果使用带有<code>WITH LOCAL CHECK OPTION</code>的视图，MySQL仅检查当前视图的规则，而不检查基础视图的规则。</p>
<h4 id="视图的作用"><a href="#视图的作用" class="headerlink" title="视图的作用"></a>视图的作用</h4><p>简单、安全、数据独立</p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428104227755.png" alt="image-20230428104227755"></p>
<p>业务需求</p>
<ol>
<li>屏蔽字段</li>
<li>多表联查</li>
</ol>
<h3 id="存储过程"><a href="#存储过程" class="headerlink" title="存储过程"></a>存储过程</h3><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428105737679.png" alt="image-20230428105737679"></p>
<h4 id="特点"><a href="#特点" class="headerlink" title="特点"></a>特点</h4><ul>
<li>封装、复用</li>
<li>可以接收参数，也可以返回数据</li>
<li>减少网络交互，效率提升</li>
</ul>
<h4 id="基本语法-1"><a href="#基本语法-1" class="headerlink" title="基本语法"></a>基本语法</h4><ul>
<li>创建</li>
</ul>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">create procedure 存储过程名称[参数列表]</span><br><span class="line">begin</span><br><span class="line"> --SQL语句</span><br><span class="line">end;</span><br></pre></td></tr></table></figure>

<ul>
<li>调用</li>
</ul>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">call 名称[参数];</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="comment">-- 存储过程基本语法</span></span><br><span class="line"><span class="comment">-- 创建</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> p1()</span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line">    <span class="keyword">select</span> <span class="built_in">count</span>(<span class="operator">*</span>) <span class="keyword">from</span> student;</span><br><span class="line"><span class="keyword">end</span>;</span><br><span class="line"><span class="comment">-- 调用</span></span><br><span class="line"><span class="keyword">call</span> p1();</span><br><span class="line"><span class="comment">-- 查看</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> information_schema.ROUTINES <span class="keyword">where</span> ROUTINE_SCHEMA <span class="operator">=</span> <span class="string">&#x27;test_db&#x27;</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">create</span> <span class="keyword">procedure</span> p1;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 删除</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">procedure</span> if <span class="keyword">exists</span> p1;</span><br></pre></td></tr></table></figure>

<blockquote>
<p><font color='cpink'>注意：在命令行中，执行创建存储过程的SQL时，需要通过关键字<code>delimiter</code>指定SQL语句的结束符。</font></p>
</blockquote>
<h4 id="变量"><a href="#变量" class="headerlink" title="变量"></a>变量</h4><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428111355600.png" alt="image-20230428111355600"></p>
<blockquote>
<p><font color='cpink'>注意：</font></p>
<p>​ <font color='cpink'>如果没有指定SESSION&#x2F;GLOBAL，默认是SESSION，会话变量。</font></p>
<p>​ <font color='cpink'>mysql服务器重启之后，所设置的全局参数会失效，要想不失效，可以在&#x2F;etc&#x2F;my.cnf 中配置。</font></p>
</blockquote>
<h5 id="用户自定义变量"><a href="#用户自定义变量" class="headerlink" title="用户自定义变量"></a>用户自定义变量</h5><p>用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL</p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428112424788.png" alt="image-20230428112424788"></p>
<h5 id="局部变量"><a href="#局部变量" class="headerlink" title="局部变量"></a>局部变量</h5><p>是根据需要定义的在局部生效的变量，访问之前需要declare声明，可用作存储过程内的局部变量和输入参数，局部变量的作用范围在其内声明的begin和end之间</p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428112706053.png" alt="image-20230428112706053"></p>
<ul>
<li>e.g.</li>
</ul>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428113133264.png" alt="image-20230428113133264"></p>
<h4 id="参数"><a href="#参数" class="headerlink" title="参数"></a>参数</h4><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428113323729.png" alt="image-20230428113323729"></p>
<ul>
<li>e.g.</li>
</ul>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428113539954.png" alt="image-20230428113539954"></p>
<h4 id="case"><a href="#case" class="headerlink" title="case"></a>case</h4><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428113908667.png" alt="image-20230428113908667"></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> p1(<span class="keyword">in</span> <span class="keyword">month</span> <span class="type">int</span>)</span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line">    <span class="keyword">declare</span> <span class="keyword">result</span> <span class="type">varchar</span>(<span class="number">10</span>) unicode;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">case</span></span><br><span class="line">        <span class="keyword">when</span> <span class="keyword">month</span> <span class="operator">&gt;=</span> <span class="number">1</span> <span class="keyword">and</span> <span class="keyword">month</span> <span class="operator">&lt;=</span> <span class="number">3</span> <span class="keyword">then</span></span><br><span class="line">            <span class="keyword">set</span> <span class="keyword">result</span> :<span class="operator">=</span> <span class="string">&#x27;第一季度&#x27;</span>;</span><br><span class="line">        <span class="keyword">when</span> <span class="keyword">month</span> <span class="operator">&gt;=</span> <span class="number">4</span> <span class="keyword">and</span> <span class="keyword">month</span> <span class="operator">&lt;=</span> <span class="number">6</span> <span class="keyword">then</span></span><br><span class="line">            <span class="keyword">set</span> <span class="keyword">result</span> :<span class="operator">=</span> <span class="string">&#x27;第二季度&#x27;</span>;</span><br><span class="line">        <span class="keyword">when</span> <span class="keyword">month</span> <span class="operator">&gt;=</span> <span class="number">7</span> <span class="keyword">and</span> <span class="keyword">month</span> <span class="operator">&lt;=</span> <span class="number">9</span> <span class="keyword">then</span></span><br><span class="line">            <span class="keyword">set</span> <span class="keyword">result</span> :<span class="operator">=</span> <span class="string">&#x27;第三季度&#x27;</span>;</span><br><span class="line">        <span class="keyword">when</span> <span class="keyword">month</span> <span class="operator">&gt;=</span> <span class="number">10</span> <span class="keyword">and</span> <span class="keyword">month</span> <span class="operator">&lt;=</span> <span class="number">12</span> <span class="keyword">then</span></span><br><span class="line">            <span class="keyword">set</span> <span class="keyword">result</span> :<span class="operator">=</span> <span class="string">&#x27;第四季度&#x27;</span>;</span><br><span class="line">    <span class="keyword">end</span> <span class="keyword">case</span> ;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">select</span> concat(<span class="string">&#x27;您输入的月份为：&#x27;</span>,<span class="keyword">month</span>,<span class="string">&#x27;所属的季度为：&#x27;</span>,<span class="keyword">result</span>);</span><br><span class="line"><span class="keyword">end</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">call</span> p1(<span class="number">9</span>);</span><br></pre></td></tr></table></figure>

<h4 id="while"><a href="#while" class="headerlink" title="while"></a>while</h4><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> p2(<span class="keyword">in</span> n <span class="type">int</span>)</span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line">    <span class="keyword">declare</span> sum <span class="type">int</span> <span class="keyword">default</span> <span class="number">0</span>;</span><br><span class="line">    while n<span class="operator">&gt;</span><span class="number">0</span> do</span><br><span class="line">        <span class="keyword">set</span> sum :<span class="operator">=</span> sum <span class="operator">+</span> n;</span><br><span class="line">        <span class="keyword">set</span> n :<span class="operator">=</span> n <span class="operator">-</span> <span class="number">1</span>;</span><br><span class="line">        <span class="keyword">end</span> while;</span><br><span class="line">    <span class="keyword">select</span> sum;</span><br><span class="line"><span class="keyword">end</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">call</span> p2(<span class="number">100</span>);</span><br></pre></td></tr></table></figure>

<h4 id="repeat"><a href="#repeat" class="headerlink" title="repeat"></a>repeat</h4><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> p3(<span class="keyword">in</span> n <span class="type">int</span>)</span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line">    <span class="keyword">declare</span> sum <span class="type">int</span> <span class="keyword">default</span> <span class="number">0</span>;</span><br><span class="line">    repeat</span><br><span class="line">        <span class="keyword">set</span> sum :<span class="operator">=</span> sum <span class="operator">+</span> n;</span><br><span class="line">        <span class="keyword">set</span> n :<span class="operator">=</span> n <span class="operator">-</span> <span class="number">1</span>;</span><br><span class="line">    until n<span class="operator">&lt;=</span><span class="number">0</span> <span class="keyword">end</span> repeat;</span><br><span class="line">    <span class="keyword">select</span> sum;</span><br><span class="line"><span class="keyword">end</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">call</span> p3(<span class="number">100</span>);</span><br></pre></td></tr></table></figure>

<h4 id="loop"><a href="#loop" class="headerlink" title="loop"></a>loop</h4><p>LOOP实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合以下两个语句使用:</p>
<ul>
<li>LEAVE:配合循环使用，退出循环。</li>
<li>ITERATE:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。</li>
</ul>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> p4(<span class="keyword">in</span> n <span class="type">int</span>)</span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line">    <span class="keyword">declare</span> sum <span class="type">int</span> <span class="keyword">default</span> <span class="number">0</span>;</span><br><span class="line"></span><br><span class="line">    sum:loop</span><br><span class="line">        if n <span class="operator">&lt;=</span> <span class="number">0</span> <span class="keyword">then</span></span><br><span class="line">            leave sum;</span><br><span class="line">        <span class="keyword">end</span> if;</span><br><span class="line">        <span class="keyword">set</span> sum :<span class="operator">=</span> sum <span class="operator">+</span> n;</span><br><span class="line">        <span class="keyword">set</span> n :<span class="operator">=</span> n <span class="number">-1</span>;</span><br><span class="line">    <span class="keyword">end</span> loop sum;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">select</span> sum;</span><br><span class="line"><span class="keyword">end</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">call</span> p4(<span class="number">100</span>);</span><br><span class="line"></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> p5(<span class="keyword">in</span> n <span class="type">int</span>)</span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line">    <span class="keyword">declare</span> sum <span class="type">int</span> <span class="keyword">default</span> <span class="number">0</span>;</span><br><span class="line"></span><br><span class="line">    sum:loop</span><br><span class="line">        if n <span class="operator">&lt;=</span> <span class="number">0</span> <span class="keyword">then</span></span><br><span class="line">            leave sum;</span><br><span class="line">        <span class="keyword">end</span> if;</span><br><span class="line"></span><br><span class="line">        if n<span class="operator">%</span><span class="number">2</span> <span class="operator">=</span> <span class="number">1</span> <span class="keyword">then</span></span><br><span class="line">            <span class="keyword">set</span> n :<span class="operator">=</span> n <span class="number">-1</span>;</span><br><span class="line">            iterate sum;</span><br><span class="line">        <span class="keyword">end</span> if;</span><br><span class="line"></span><br><span class="line">        <span class="keyword">set</span> sum :<span class="operator">=</span> sum <span class="operator">+</span> n;</span><br><span class="line">        <span class="keyword">set</span> n :<span class="operator">=</span> n <span class="number">-1</span>;</span><br><span class="line">    <span class="keyword">end</span> loop sum;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">select</span> sum;</span><br><span class="line"><span class="keyword">end</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">call</span> p5(<span class="number">10</span>);</span><br></pre></td></tr></table></figure>

<h4 id="游标"><a href="#游标" class="headerlink" title="游标"></a>游标</h4><p>游标〈CURSOR）是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和 CLOSE，其语法分别如下。</p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428125555603.png" alt="image-20230428125555603"></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> p6(<span class="keyword">in</span> uage <span class="type">int</span>)</span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line">    <span class="keyword">declare</span> uname <span class="type">varchar</span>(<span class="number">100</span>);</span><br><span class="line">    <span class="keyword">declare</span> upro <span class="type">varchar</span>(<span class="number">100</span>);</span><br><span class="line">    <span class="keyword">declare</span> u_cursor <span class="keyword">cursor</span> <span class="keyword">for</span> <span class="keyword">select</span> name,profession <span class="keyword">from</span> tb_user <span class="keyword">where</span> age <span class="operator">&lt;=</span> uage;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">drop</span> <span class="keyword">table</span> if <span class="keyword">exists</span> tb_user_pro;</span><br><span class="line">    <span class="keyword">create</span> <span class="keyword">table</span> if <span class="keyword">not</span> <span class="keyword">exists</span> tb_user_pro(</span><br><span class="line">        id <span class="type">int</span> <span class="keyword">primary</span> key auto_increment,</span><br><span class="line">        name <span class="type">varchar</span>(<span class="number">100</span>),</span><br><span class="line">        profession <span class="type">varchar</span>(<span class="number">100</span>)</span><br><span class="line">    );</span><br><span class="line">    <span class="keyword">open</span> u_cursor;</span><br><span class="line">    while <span class="literal">true</span> do</span><br><span class="line">        <span class="keyword">fetch</span> u_cursor <span class="keyword">into</span> uname,upro;</span><br><span class="line">        <span class="keyword">insert</span> <span class="keyword">into</span> tb_user_pro <span class="keyword">values</span> (<span class="keyword">null</span>, uname, upro);</span><br><span class="line">        <span class="keyword">end</span> while;</span><br><span class="line">    <span class="keyword">close</span> u_cursor;</span><br><span class="line"><span class="keyword">end</span>;</span><br></pre></td></tr></table></figure>

<h4 id="条件处理程序（handler）"><a href="#条件处理程序（handler）" class="headerlink" title="条件处理程序（handler）"></a>条件处理程序（handler）</h4><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">declare</span> exit handler <span class="keyword">for</span> <span class="keyword">SQLSTATE</span> <span class="string">&#x27;02000&#x27;</span> <span class="keyword">close</span> u_cursor;</span><br><span class="line"></span><br><span class="line"><span class="keyword">declare</span> exit handler <span class="keyword">for</span> <span class="keyword">SQLSTATE</span> <span class="keyword">not</span> found <span class="keyword">close</span> u_cursor;</span><br></pre></td></tr></table></figure>

<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428161214748.png" alt="image-20230428161214748"></p>
<h3 id="存储函数"><a href="#存储函数" class="headerlink" title="存储函数"></a>存储函数</h3><p>存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="comment">-- 存储函数</span></span><br><span class="line"><span class="comment">-- 从1到n的累加</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">function</span> fun1(n <span class="type">int</span>)</span><br><span class="line"><span class="keyword">returns</span> <span class="type">int</span> <span class="keyword">deterministic</span></span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line">    <span class="keyword">declare</span> sum <span class="type">int</span> <span class="keyword">default</span> <span class="number">0</span>;</span><br><span class="line"></span><br><span class="line">    while n<span class="operator">&gt;</span><span class="number">0</span> do</span><br><span class="line">        <span class="keyword">set</span> sum :<span class="operator">=</span> sum <span class="operator">+</span> n;</span><br><span class="line">        <span class="keyword">set</span> n :<span class="operator">=</span> n <span class="operator">-</span> <span class="number">1</span>;</span><br><span class="line">        <span class="keyword">end</span> while;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">return</span> sum;</span><br><span class="line"><span class="keyword">end</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> fun1(<span class="number">100</span>);</span><br></pre></td></tr></table></figure>

<h3 id="触发器"><a href="#触发器" class="headerlink" title="触发器"></a>触发器</h3><h4 id="介绍"><a href="#介绍" class="headerlink" title="介绍"></a>介绍</h4><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428162016089.png" alt="image-20230428162016089"></p>
<h4 id="语法"><a href="#语法" class="headerlink" title="语法"></a>语法</h4><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428162112340.png" alt="image-20230428162112340"></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="comment">-- 插入数据触发器</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">trigger</span> tb_user_insert_trigger</span><br><span class="line">    after <span class="keyword">insert</span> <span class="keyword">on</span> tb_user <span class="keyword">for</span> <span class="keyword">each</span> <span class="type">row</span></span><br><span class="line">    <span class="keyword">begin</span></span><br><span class="line">        <span class="keyword">insert</span> <span class="keyword">into</span> user_logs(id, operation, operate_time, operate_id, operate_params)</span><br><span class="line">            <span class="keyword">values</span> (<span class="keyword">null</span>, <span class="string">&#x27;insert&#x27;</span>, now(), NEW.id, concat(<span class="string">&#x27;插入的数据内容为：id=&#x27;</span>, NEW.id, <span class="string">&#x27;,name=&#x27;</span>,NEW.name, <span class="string">&#x27;,phone=&#x27;</span>,NEW.phone, <span class="string">&#x27;email=&#x27;</span>, NEW.email, <span class="string">&#x27;,profession=&#x27;</span>, NEW.profession));</span><br><span class="line">    <span class="keyword">end</span>;</span><br><span class="line">    </span><br><span class="line"><span class="comment">-- 查看</span></span><br><span class="line"><span class="keyword">show</span> triggers;</span><br><span class="line"></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">trigger</span> tb_user_insert_trigger;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="comment">-- 修改数据触发器</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">trigger</span> tb_user_update_trigger</span><br><span class="line">    after <span class="keyword">update</span> <span class="keyword">on</span> tb_user <span class="keyword">for</span> <span class="keyword">each</span> <span class="type">row</span></span><br><span class="line">    <span class="keyword">begin</span></span><br><span class="line">        <span class="keyword">insert</span> <span class="keyword">into</span> user_logs(id, operation, operate_time, operate_id, operate_params)</span><br><span class="line">            <span class="keyword">values</span> (<span class="keyword">null</span>, <span class="string">&#x27;update&#x27;</span>, now(), NEW.id, concat(<span class="string">&#x27;更新之前的数据内容为：id=&#x27;</span>, OLD.id, <span class="string">&#x27;,name=&#x27;</span>,OLD.name, <span class="string">&#x27;,phone=&#x27;</span>,OLD.phone, <span class="string">&#x27;email=&#x27;</span>, OLD.email, <span class="string">&#x27;,profession=&#x27;</span>, OLD.profession,</span><br><span class="line">                                                          <span class="string">&#x27;更新之后的数据内容为：id=&#x27;</span>, NEW.id, <span class="string">&#x27;,name=&#x27;</span>,NEW.name, <span class="string">&#x27;,phone=&#x27;</span>,NEW.phone, <span class="string">&#x27;email=&#x27;</span>, NEW.email, <span class="string">&#x27;,profession=&#x27;</span>, NEW.profession));</span><br><span class="line">    <span class="keyword">end</span>;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="comment">-- 删除数据触发器</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">trigger</span> tb_user_delete_trigger</span><br><span class="line">    after <span class="keyword">delete</span> <span class="keyword">on</span> tb_user <span class="keyword">for</span> <span class="keyword">each</span> <span class="type">row</span></span><br><span class="line">    <span class="keyword">begin</span></span><br><span class="line">        <span class="keyword">insert</span> <span class="keyword">into</span> user_logs(id, operation, operate_time, operate_id, operate_params)</span><br><span class="line">            <span class="keyword">values</span> (<span class="keyword">null</span>, <span class="string">&#x27;delete&#x27;</span>, now(), OLD.id, concat(<span class="string">&#x27;删除之前的数据内容为：id=&#x27;</span>, OLD.id, <span class="string">&#x27;,name=&#x27;</span>,OLD.name, <span class="string">&#x27;,phone=&#x27;</span>,OLD.phone, <span class="string">&#x27;email=&#x27;</span>, OLD.email, <span class="string">&#x27;,profession=&#x27;</span>, OLD.profession));</span><br><span class="line">    <span class="keyword">end</span>;</span><br></pre></td></tr></table></figure>

<p>通过触发器记录tb_user表的数据变更日志</p>
<h2 id="锁"><a href="#锁" class="headerlink" title="锁"></a>锁</h2><p>锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I&#x2F;O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。</p>
<ol>
<li>全局锁：锁定数据库中的所有表</li>
<li>表级锁：每次操作锁住整张表</li>
<li>行级锁：每次操作锁住对应的行数据</li>
</ol>
<h3 id="全局锁"><a href="#全局锁" class="headerlink" title="全局锁"></a>全局锁</h3><p>全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句、DDL语句，已经更新操作的事务提交语句都将被阻塞。</p>
<p>其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。</p>
<p><strong><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428172353803.png" alt="image-20230428172353803"></strong></p>
<h4 id="特点-1"><a href="#特点-1" class="headerlink" title="特点"></a>特点</h4><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428172516785.png" alt="image-20230428172516785"></p>
<h3 id="表级锁"><a href="#表级锁" class="headerlink" title="表级锁"></a>表级锁</h3><p>每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。</p>
<p>对于表级锁，主要分为以下三类：</p>
<h4 id="1-表锁"><a href="#1-表锁" class="headerlink" title="1.表锁"></a>1.表锁</h4><ul>
<li><p>表共享读锁（read lock）</p>
</li>
<li><p>表独占写锁（write lock）</p>
</li>
<li><p>语法</p>
<ol>
<li>加锁：lock tables 表名… read&#x2F;write</li>
<li>释放锁：unlock tables &#x2F;客户端断开连接</li>
</ol>
</li>
</ul>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428173304943.png" alt="image-20230428173304943"></p>
<p><font color='cpink'>读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。</font></p>
<h4 id="2-元数据锁（meta-data-lock，MDL）"><a href="#2-元数据锁（meta-data-lock，MDL）" class="headerlink" title="2.元数据锁（meta data lock，MDL）"></a>2.元数据锁（meta data lock，MDL）</h4><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428175710190.png" alt="image-20230428175710190"></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span> object_type,object_schema,object_name,lock_type,lock_duration <span class="keyword">from</span> performance_schema.metadata_locks;</span><br></pre></td></tr></table></figure>

<h4 id="3-意向锁"><a href="#3-意向锁" class="headerlink" title="3.意向锁"></a>3.意向锁</h4><p>为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。</p>
<ul>
<li>意向共享锁（IS）：由语句select…lock in share mode添加<ul>
<li>与表锁共享锁（read）兼容，与表锁排它锁（write）互斥</li>
</ul>
</li>
<li>意向排他锁（IX）：由insert、update、delete、select…for update添加<ul>
<li>与表锁共享锁（read）及表锁排它锁（write）都互斥，意向锁之间不会互斥</li>
</ul>
</li>
</ul>
<p>可以通过以下SQL，查看意向锁及行锁的加锁情况：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span> object_type,object_schema,object_name,lock_type,lock_duration <span class="keyword">from</span> performance_schema.data_locks;</span><br></pre></td></tr></table></figure>

<h3 id="行级锁"><a href="#行级锁" class="headerlink" title="行级锁"></a>行级锁</h3><p>每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。</p>
<p>InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：</p>
<ol>
<li>行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此进行update和delete。在RC、RR隔离级别下都支持</li>
<li>间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持</li>
<li>临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持</li>
</ol>
<ul>
<li>行锁<ol>
<li>共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁</li>
<li>排它锁（X）：允许获取排它锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排它锁</li>
</ol>
</li>
</ul>
<table>
<thead>
<tr>
<th align="center">当前锁类型\请求锁类型</th>
<th align="center">S（共享锁）</th>
<th align="center">X（排他锁）</th>
</tr>
</thead>
<tbody><tr>
<td align="center">S（共享锁）</td>
<td align="center">兼容</td>
<td align="center">冲突</td>
</tr>
<tr>
<td align="center">X（排他锁）</td>
<td align="center">冲突</td>
<td align="center">冲突</td>
</tr>
</tbody></table>
<table>
<thead>
<tr>
<th align="left">SQL</th>
<th align="left">行锁类型</th>
<th align="left">说明</th>
</tr>
</thead>
<tbody><tr>
<td align="left">INSERT…</td>
<td align="left">排他锁</td>
<td align="left">自动加锁</td>
</tr>
<tr>
<td align="left">UPDATE…</td>
<td align="left">排他锁</td>
<td align="left">自动加锁</td>
</tr>
<tr>
<td align="left">DELETE…</td>
<td align="left">排他锁</td>
<td align="left">自动加锁</td>
</tr>
<tr>
<td align="left">SELECT（正常）</td>
<td align="left"><font color='cpink'>不加任何锁</font></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">SELECT…LOCK IN SHARE MODE</td>
<td align="left">共享锁</td>
<td align="left">需要手动在SELECT之后加LOCK IN SHARE MODE</td>
</tr>
<tr>
<td align="left">SELECT…FOR UPDATE</td>
<td align="left">排他锁</td>
<td align="left">需要手动在SELECT之后加FOR UPDATE</td>
</tr>
</tbody></table>
<p>默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。</p>
<ul>
<li>行锁</li>
</ul>
<ol>
<li>针对唯一索引进行检索时，对已存在的记录进行等值匹配，将会自动优化为行锁</li>
<li>InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会 <font color='cpink'>升级为表锁。 </font></li>
</ol>
<ul>
<li>间隙锁&#x2F;临键锁</li>
</ul>
<ol>
<li>索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。</li>
<li>索引上的等值查询（普通索引），向右遍历最后一个值不满足查询需求时，next-key lock 退化为间隙锁。</li>
<li>索引上的范围查询（唯一索引）–会访问到不满足条件的第一个值为止。</li>
</ol>
<blockquote>
<p>注意：间隙锁唯一的目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁</p>
</blockquote>
<h2 id="InnoDB引擎"><a href="#InnoDB引擎" class="headerlink" title="InnoDB引擎"></a>InnoDB引擎</h2><h3 id="逻辑存储结构"><a href="#逻辑存储结构" class="headerlink" title="逻辑存储结构"></a>逻辑存储结构</h3><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428192502367.png" alt="image-20230428192502367"></p>
<h3 id="架构"><a href="#架构" class="headerlink" title="架构"></a>架构</h3><p>MySQL5.5版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是lnnoDB架构图，左侧为内存结构，右侧为磁盘结构。</p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428192643577.png" alt="image-20230428192643577"></p>
<h4 id="内存结构"><a href="#内存结构" class="headerlink" title="内存结构"></a>内存结构</h4><h5 id="Buffer-Pool"><a href="#Buffer-Pool" class="headerlink" title="Buffer Pool"></a>Buffer Pool</h5><p><img src="C:\Users\Frode\AppData\Roaming\Typora\typora-user-images\image-20230428192945264.png" alt="image-20230428192945264"></p>
<h5 id="Change-Buffer"><a href="#Change-Buffer" class="headerlink" title="Change Buffer"></a>Change Buffer</h5><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428193112003.png" alt="image-20230428193112003"></p>
<h5 id="自适应hash"><a href="#自适应hash" class="headerlink" title="自适应hash"></a>自适应hash</h5><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428193320431.png" alt="image-20230428193320431"></p>
<h5 id="Log-Buffer"><a href="#Log-Buffer" class="headerlink" title="Log Buffer"></a>Log Buffer</h5><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428193422849.png" alt="image-20230428193422849"></p>
<h4 id="磁盘结构"><a href="#磁盘结构" class="headerlink" title="磁盘结构"></a>磁盘结构</h4><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428193755772.png" alt="image-20230428193755772"></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span>space ts_itheima <span class="keyword">add</span> datafile <span class="string">&#x27;myitheima.ibd&#x27;</span> engine <span class="operator">=</span> innodb;</span><br></pre></td></tr></table></figure>

<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428194426610.png" alt="image-20230428194426610"></p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428194552842.png" alt="image-20230428194552842"></p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428194819213.png" alt="image-20230428194819213"></p>
<h3 id="事务原理"><a href="#事务原理" class="headerlink" title="事务原理"></a>事务原理</h3><ul>
<li>事务</li>
</ul>
<p>事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失效。</p>
<ul>
<li>特性</li>
</ul>
<p><strong>原子性</strong>(Atomicity): 事务是不可分割的最小操作单元，要么全部成功，要么全部失败。<br><strong>一致性</strong>(Consistency): 事务完成时，必须使所有的数据都保持一致状态。<br><strong>隔离性</strong>(Isolation): 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。<br><strong>持久性</strong>(Durability): 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。</p>
<h4 id="redo-log"><a href="#redo-log" class="headerlink" title="redo log"></a>redo log</h4><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428200318285.png" alt="image-20230428200318285"></p>
<h4 id="undo-log"><a href="#undo-log" class="headerlink" title="undo log"></a>undo log</h4><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428200547235.png" alt="image-20230428200547235"></p>
<h3 id="MVCC"><a href="#MVCC" class="headerlink" title="MVCC"></a>MVCC</h3><p>多版本并发控制（<font color='red'><strong>高频面试题</strong></font>）</p>
<ul>
<li>当前读</li>
</ul>
<p>读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select … lock in share mode（共享锁），select … for update、update、insert、delete（排他锁）都是一种当前读。</p>
<ul>
<li>快照读</li>
</ul>
<p>简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。</p>
<ul>
<li><p>Read Committed：每次select，都生成一个快照读。</p>
</li>
<li><p>Repeatable Read：开启事务后第一个select语句才是快照读的地方。</p>
</li>
<li><p>Serializable：快照读会退化为当前读。</p>
</li>
<li><p>MVCC</p>
</li>
</ul>
<p>全称Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段，undo log日志、readView。</p>
<h4 id="记录中的隐藏字段"><a href="#记录中的隐藏字段" class="headerlink" title="记录中的隐藏字段"></a>记录中的隐藏字段</h4><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428202126310.png" alt="image-20230428202126310"></p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">cd /var/lib/mysql/test_db</span><br><span class="line"></span><br><span class="line">idb2sdi stu.ibd</span><br></pre></td></tr></table></figure>

<h4 id="undo-log日志"><a href="#undo-log日志" class="headerlink" title="undo log日志"></a>undo log日志</h4><p>回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。<br>当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。<br>而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。</p>
<ul>
<li>undo log版本链</li>
</ul>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428203308445.png" alt="image-20230428203308445"></p>
<h4 id="readview"><a href="#readview" class="headerlink" title="readview"></a>readview</h4><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428203512375.png" alt="image-20230428203512375"></p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428203651538.png" alt="image-20230428203651538"></p>
<h5 id="RC"><a href="#RC" class="headerlink" title="RC"></a>RC</h5><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428204219025.png" alt="image-20230428204219025"></p>
<h5 id="RR"><a href="#RR" class="headerlink" title="RR"></a>RR</h5><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428204546699.png" alt="image-20230428204546699"></p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428204651375.png" alt="image-20230428204651375"></p>
<h2 id="MySQL系统管理"><a href="#MySQL系统管理" class="headerlink" title="MySQL系统管理"></a>MySQL系统管理</h2><h3 id="系统数据库"><a href="#系统数据库" class="headerlink" title="系统数据库"></a>系统数据库</h3><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428205344425.png" alt="image-20230428205344425"></p>
<h3 id="常用工具"><a href="#常用工具" class="headerlink" title="常用工具"></a>常用工具</h3><h4 id="mysql-1"><a href="#mysql-1" class="headerlink" title="mysql"></a>mysql</h4><figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">mysql -h192.168.88.130 -P3306 -uroot -p123 itcast -e &quot;select * from stu&quot;</span><br></pre></td></tr></table></figure>

<p>-e选项可以在Mysql客户端执行SQL语句，而不用连接到MySQL数据库再执行，主要用于一些批处理脚本。</p>
<h4 id="mysqladmin"><a href="#mysqladmin" class="headerlink" title="mysqladmin"></a>mysqladmin</h4><p>是一个执行管理操作的客户端程序，可以用它来检查服务器的配置和当前状态，创建并删除数据库等。</p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">mysqladmin -uroot -p123 drop &#x27;test01&#x27;</span><br><span class="line">mysqladmin -uroot -p123 drop version</span><br></pre></td></tr></table></figure>

<h4 id="mysqlbinlog"><a href="#mysqlbinlog" class="headerlink" title="mysqlbinlog"></a>mysqlbinlog</h4><p>由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog日志管理工具。</p>
<p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428210834712.png" alt="image-20230428210834712"></p>
<h4 id="mysqlshow"><a href="#mysqlshow" class="headerlink" title="mysqlshow"></a>mysqlshow</h4><p>客户端对象查找工具，用来很快地查找存在哪些数据库，数据库中的表、表中的列或者索引。</p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">选项：</span><br><span class="line"> --count 显示数据库及表的统计信息（数据库，表均可以不指定）</span><br><span class="line"> -i  显示指定数据库或者指定表的状态信息</span><br><span class="line"># 查询每个数据库的表的数量及表中记录的数量</span><br><span class="line">mysqlshow -uroot -p123 --count</span><br><span class="line"></span><br><span class="line"># 查询test库中每个表中的字段数及行数</span><br><span class="line">mysqlshow -uroot -p123 test --count</span><br><span class="line"></span><br><span class="line"># 查询test库中book表的详细情况</span><br><span class="line">mysqlshow -uroot -p123 test book --count</span><br></pre></td></tr></table></figure>

<h4 id="mysqldump"><a href="#mysqldump" class="headerlink" title="mysqldump"></a>mysqldump</h4><p><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/typora/image-20230428212123026.png" alt="image-20230428212123026"></p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">mysqldump -uroot -p123 db01 &gt; db01.sql</span><br><span class="line"></span><br><span class="line">mysqldump -uroot -p123 -T /var/lib/mysql-files/ db01 score</span><br></pre></td></tr></table></figure>

<h4 id="mysqlimport-x2F-source"><a href="#mysqlimport-x2F-source" class="headerlink" title="mysqlimport&#x2F;source"></a>mysqlimport&#x2F;source</h4><p>客户端数据导入工具，用来导入mysqldump 加-T参数导出的文本文件</p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">mysqlimport -uroot -p123 test /var/lib/mysql-files/score.txt</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 如果需要导入<span class="keyword">sql</span>文件，可以使用mysql中的source指令</span><br><span class="line">source <span class="operator">/</span>root<span class="operator">/</span>xxx.sql</span><br></pre></td></tr></table></figure>

<h2 id="Note"><a href="#Note" class="headerlink" title="Note"></a>Note</h2><h3 id="MySQL-中隔离级别-RC-与-RR-的区别"><a href="#MySQL-中隔离级别-RC-与-RR-的区别" class="headerlink" title="MySQL 中隔离级别 RC 与 RR 的区别"></a><a target="_blank" rel="noopener" href="https://www.cnblogs.com/digdeep/p/4968453.html">MySQL 中隔离级别 RC 与 RR 的区别</a></h3><p><font color='red'><strong>1. 数据库事务ACID特性</strong></font></p>
<p><strong>数据库事务的4个特性：<br>原子性(Atomic):</strong> 事务中的多个操作，不可分割，要么都成功，要么都失败； All or Nothing.<br><strong>一致性(Consistency):</strong> 事务操作之后, 数据库所处的状态和业务规则是一致的; 比如a,b账户相互转账之后，总金额不变；<br><strong>隔离性(Isolation):</strong> 多个事务之间就像是串行执行一样，不相互影响;<br><strong>持久性(Durability):</strong> 事务提交后被持久化到永久存储.</p>
<p><font color='red'><strong>2. 隔离性</strong></font></p>
<p>其中 <strong>隔离性</strong> 分为了四种：</p>
<p><strong>READ UNCOMMITTED</strong>：可以读取未提交的数据，未提交的数据称为脏数据，所以又称脏读。此时：幻读，不可重复读和脏读均允许；<br><strong>READ COMMITTED</strong>：只能读取已经提交的数据；此时：允许幻读和不可重复读，但不允许脏读，所以RC隔离级别要求解决脏读；<br><strong>REPEATABLE READ</strong>：同一个事务中多次执行同一个select,读取到的数据没有发生改变；此时：允许幻读，但不允许不可重复读和脏读，所以RR隔离级别要求解决不可重复读；<br><strong>SERIALIZABLE</strong>: 幻读，不可重复读和脏读都不允许，所以serializable要求解决幻读；</p>
<p><font color='red'><strong>3. 几个概念</strong></font></p>
<p><strong>脏读</strong>：可以读取未提交的数据。RC 要求解决脏读；</p>
<p><strong>不可重复读</strong>：同一个事务中多次执行同一个select, 读取到的数据发生了改变(被其它事务update并且提交)；</p>
<p><strong>可重复读</strong>：同一个事务中多次执行同一个select, 读取到的数据没有发生改变(一般使用MVCC实现)；RR各级级别要求达到可重复读的标准；</p>
<p><strong>幻读</strong>：同一个事务中多次执行同一个select, 读取到的数据行发生改变。也就是行数减少或者增加了(被其它事务delete&#x2F;insert并且提交)。SERIALIZABLE要求解决幻读问题；</p>
<p><strong>这里一定要区分 不可重复读 和 幻读：</strong></p>
<p>不可重复读的重点是<strong>修改</strong>:<br>同样的条件的select, 你读取过的数据, 再次读取出来发现值不一样了</p>
<p>幻读的重点在于<strong>新增或者删除</strong>:<br>同样的条件的select, 第1次和第2次读出来的记录数不一样</p>
<p>从结果上来看, 两者都是为多次读取的结果不一致。但如果你从实现的角度来看, 它们的区别就比较大：<br>对于前者, 在RC下只需要锁住满足条件的记录，就可以避免被其它事务修改，也就是 select for update, select in share mode; RR隔离下使用MVCC实现可重复读；<br>对于后者, 要锁住满足条件的记录及所有这些记录之间的gap，也就是需要 gap lock。</p>
<p>而ANSI SQL标准没有从隔离程度进行定义，而是定义了事务的隔离级别，同时定义了不同事务隔离级别解决的三大并发问题：</p>
<table>
<thead>
<tr>
<th><em>Isolation Level</em></th>
<th><strong>Dirty Read</strong></th>
<th><strong>Unrepeatable Read</strong></th>
<th><strong>Phantom Read</strong></th>
</tr>
</thead>
<tbody><tr>
<td><em>Read UNCOMMITTED</em></td>
<td>YES</td>
<td>YES</td>
<td>YES</td>
</tr>
<tr>
<td><em>READ COMMITTED</em></td>
<td>NO</td>
<td>YES</td>
<td>YES</td>
</tr>
<tr>
<td><em>READ REPEATABLE</em></td>
<td>NO</td>
<td>NO</td>
<td>YES</td>
</tr>
<tr>
<td><em>SERIALIZABLE</em></td>
<td>NO</td>
<td>NO</td>
<td>NO</td>
</tr>
</tbody></table>
<p>参见：你真的明白事务的隔离性吗？ (姜承尧)</p>
<p><font color='red'><strong>4. 数据库的默认隔离级别</strong></font></p>
<p>除了MySQL默认采用RR隔离级别之外，其它几大数据库都是采用RC隔离级别。</p>
<p>但是他们的实现也是极其不一样的。Oracle仅仅实现了RC 和 SERIALIZABLE隔离级别。默认采用RC隔离级别，解决了脏读。但是允许不可重复读和幻读。其SERIALIZABLE则解决了脏读、不可重复读、幻读。</p>
<p>MySQL的实现：MySQL默认采用RR隔离级别，SQL标准是要求RR解决不可重复读的问题，但是因为MySQL采用了gap lock，所以实际上MySQL的RR隔离级别也解决了幻读的问题。那么MySQL的SERIALIZABLE是怎么回事呢？其实MySQL的SERIALIZABLE采用了经典的实现方式，对读和写都加锁。</p>
<p><font color='red'><strong>5. MySQL 中RC和RR隔离级别的区别</strong></font></p>
<p>MySQL数据库中默认隔离级别为RR，但是实际情况是使用RC 和 RR隔离级别的都不少。好像淘宝、网易都是使用的 RC 隔离级别。那么在MySQL中 RC 和 RR有什么区别呢？我们该如何选择呢？为什么MySQL将RR作为默认的隔离级别呢？</p>
<p><strong>5.1 RC 与 RR 在锁方面的区别</strong></p>
<p>1&gt; 显然 RR 支持 gap lock(next-key lock)，而RC则没有gap lock。因为MySQL的RR需要gap lock来解决幻读问题。而RC隔离级别则是允许存在不可重复读和幻读的。所以RC的并发一般要好于RR；</p>
<p>2&gt; RC 隔离级别，通过 where 条件过滤之后，不符合条件的记录上的行锁，会释放掉(虽然这里破坏了“两阶段加锁原则”)；但是RR隔离级别，即使不符合where条件的记录，也不会是否行锁和gap lock；所以从锁方面来看，RC的并发应该要好于RR；另外 insert into t select … from s where 语句在s表上的锁也是不一样的，</p>
</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="https://gitee.com/frode117">frode</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="https://gitee.com/frode117/2023/04/27/mysql-study/">https://gitee.com/frode117/2023/04/27/mysql-study/</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="https://gitee.com/frode117" target="_blank">mundane</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/mundane/tags/mysql/">mysql</a></div><div class="post_share"><div class="social-share" data-image="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/img/suzume.png" data-sites="twitter,wechat,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/butterfly-extsrc/sharejs/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/butterfly-extsrc/sharejs/dist/js/social-share.min.js" defer></script></div></div><nav class="pagination-post" id="pagination"><div class="next-post pull-full"><a href="/mundane/2023/04/27/hadoop-study/" title="hadoop学习"><img class="cover" src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/img/67190321_p0.png" onerror="onerror=null;src='/mundane/img/404.jpg'" alt="cover of next post"><div class="pagination-info"><div class="label">下一篇</div><div class="next_info">hadoop学习</div></div></a></div></nav><hr/><div id="post-comment"><div class="comment-head"><div class="comment-headline"><i class="fas fa-comments fa-fw"></i><span> 评论</span></div></div><div class="comment-wrap"><div><div class="vcomment" id="vcomment"></div></div></div></div></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="is-center"><div class="avatar-img"><img src="/mundane/img/violet.jpg" onerror="this.onerror=null;this.src='/mundane/img/friend_404.gif'" alt="avatar"/></div><div class="author-info__name">frode</div><div class="author-info__description">this is my blog</div></div><div class="card-info-data site-data is-center"><a href="/mundane/archives/"><div class="headline">文章</div><div class="length-num">4</div></a><a href="/mundane/tags/"><div class="headline">标签</div><div class="length-num">8</div></a><a href="/mundane/categories/"><div class="headline">分类</div><div class="length-num">4</div></a></div><a id="card-info-btn" target="_blank" rel="noopener" href="https://github.com/munDane117"><i class="fab fa-github"></i><span>Follow Me</span></a></div><div class="card-widget card-announcement"><div class="item-headline"><i class="fas fa-bullhorn fa-shake"></i><span>公告</span></div><div class="announcement_content">stay hungry,stay foolish</div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span><span class="toc-percentage"></span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#mysql"><span class="toc-number">1.</span> <span class="toc-text">mysql</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%9F%BA%E6%9C%AC%E8%AF%AD%E6%B3%95"><span class="toc-number">1.1.</span> <span class="toc-text">基本语法</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%89%A7%E8%A1%8C%E9%A1%BA%E5%BA%8F"><span class="toc-number">1.1.1.</span> <span class="toc-text">执行顺序</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%87%BD%E6%95%B0"><span class="toc-number">1.2.</span> <span class="toc-text">函数</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E7%BA%A6%E6%9D%9F"><span class="toc-number">1.3.</span> <span class="toc-text">约束</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%BA%A6%E6%9D%9F-%E5%A4%96%E9%94%AE"><span class="toc-number">1.3.1.</span> <span class="toc-text">约束-外键</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%A4%9A%E8%A1%A8%E5%85%B3%E7%B3%BB"><span class="toc-number">1.4.</span> <span class="toc-text">多表关系</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.4.1.</span> <span class="toc-text">多表查询</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%BA%8B%E5%8A%A1"><span class="toc-number">1.5.</span> <span class="toc-text">事务</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BA%8B%E5%8A%A1%E5%9B%9B%E5%A4%A7%E7%89%B9%E6%80%A7%EF%BC%88ACID%EF%BC%89"><span class="toc-number">1.5.1.</span> <span class="toc-text">事务四大特性（ACID）</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#mysql%E4%BD%93%E7%B3%BB%E7%BB%93%E6%9E%84"><span class="toc-number">1.6.</span> <span class="toc-text">mysql体系结构</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E"><span class="toc-number">1.7.</span> <span class="toc-text">存储引擎</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#InnoDB"><span class="toc-number">1.7.1.</span> <span class="toc-text">InnoDB</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#MyISAM"><span class="toc-number">1.7.2.</span> <span class="toc-text">MyISAM</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#Memory"><span class="toc-number">1.7.3.</span> <span class="toc-text">Memory</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E9%9D%A2%E8%AF%95%E5%B8%B8%E9%97%AE"><span class="toc-number">1.7.4.</span> <span class="toc-text">面试常问</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E%E9%80%89%E6%8B%A9"><span class="toc-number">1.7.5.</span> <span class="toc-text">存储引擎选择</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95"><span class="toc-number">1.8.</span> <span class="toc-text">索引</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95%E6%A6%82%E8%BF%B0"><span class="toc-number">1.8.1.</span> <span class="toc-text">索引概述</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95%E7%BB%93%E6%9E%84"><span class="toc-number">1.8.2.</span> <span class="toc-text">索引结构</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%BA%8C%E5%8F%89%E6%A0%91"><span class="toc-number">1.8.2.1.</span> <span class="toc-text">二叉树</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#B-Tree"><span class="toc-number">1.8.2.2.</span> <span class="toc-text">B+Tree</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#hash"><span class="toc-number">1.8.2.3.</span> <span class="toc-text">hash</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#Hash%E7%B4%A2%E5%BC%95%E7%89%B9%E7%82%B9"><span class="toc-number">1.8.2.4.</span> <span class="toc-text">Hash索引特点</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%B8%BA%E4%BB%80%E4%B9%88InnoDB%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E%E9%80%89%E6%8B%A9%E4%BD%BF%E7%94%A8B-Tree%E7%B4%A2%E5%BC%95%E7%BB%93%E6%9E%84%EF%BC%9F"><span class="toc-number">1.8.2.5.</span> <span class="toc-text">为什么InnoDB存储引擎选择使用B+Tree索引结构？</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95%E5%88%86%E7%B1%BB"><span class="toc-number">1.8.3.</span> <span class="toc-text">索引分类</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95%E8%AF%AD%E6%B3%95"><span class="toc-number">1.8.4.</span> <span class="toc-text">索引语法</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#SQL%E6%80%A7%E8%83%BD%E5%88%86%E6%9E%90"><span class="toc-number">1.8.5.</span> <span class="toc-text">SQL性能分析</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95%E4%BD%BF%E7%94%A8"><span class="toc-number">1.8.6.</span> <span class="toc-text">索引使用</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88"><span class="toc-number">1.8.6.1.</span> <span class="toc-text">索引失效</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#%E6%9C%80%E5%B7%A6%E5%89%8D%E7%BC%80%E6%B3%95%E5%88%99"><span class="toc-number">1.8.6.1.1.</span> <span class="toc-text">最左前缀法则</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#%E8%8C%83%E5%9B%B4%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.8.6.1.2.</span> <span class="toc-text">范围查询</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95%E5%88%97%E8%BF%90%E7%AE%97"><span class="toc-number">1.8.6.1.3.</span> <span class="toc-text">索引列运算</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#%E5%AD%97%E7%AC%A6%E4%B8%B2%E4%B8%8D%E5%8A%A0%E5%BC%95%E5%8F%B7"><span class="toc-number">1.8.6.1.4.</span> <span class="toc-text">字符串不加引号</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#%E6%A8%A1%E7%B3%8A%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.8.6.1.5.</span> <span class="toc-text">模糊查询</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#or%E8%BF%9E%E6%8E%A5%E7%9A%84%E6%9D%A1%E4%BB%B6"><span class="toc-number">1.8.6.1.6.</span> <span class="toc-text">or连接的条件</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E5%88%86%E5%B8%83%E5%BD%B1%E5%93%8D"><span class="toc-number">1.8.6.1.7.</span> <span class="toc-text">数据分布影响</span></a></li></ol></li><li class="toc-item toc-level-4"><a class="toc-link" href="#SQL%E6%8F%90%E7%A4%BA"><span class="toc-number">1.8.6.2.</span> <span class="toc-text">SQL提示</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E8%A6%86%E7%9B%96%E7%B4%A2%E5%BC%95"><span class="toc-number">1.8.6.3.</span> <span class="toc-text">覆盖索引</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%89%8D%E7%BC%80%E7%B4%A2%E5%BC%95"><span class="toc-number">1.8.6.4.</span> <span class="toc-text">前缀索引</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%8D%95%E5%88%97%E7%B4%A2%E5%BC%95%E4%B8%8E%E8%81%94%E5%90%88%E7%B4%A2%E5%BC%95"><span class="toc-number">1.8.6.5.</span> <span class="toc-text">单列索引与联合索引</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95%E8%AE%BE%E8%AE%A1%E5%8E%9F%E5%88%99"><span class="toc-number">1.8.7.</span> <span class="toc-text">索引设计原则</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#SQL%E4%BC%98%E5%8C%96"><span class="toc-number">1.9.</span> <span class="toc-text">SQL优化</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%8F%92%E5%85%A5%E6%95%B0%E6%8D%AE"><span class="toc-number">1.9.1.</span> <span class="toc-text">插入数据</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#Insert%E4%BC%98%E5%8C%96"><span class="toc-number">1.9.1.1.</span> <span class="toc-text">Insert优化</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#%E6%89%B9%E9%87%8F%E6%8F%92%E5%85%A5"><span class="toc-number">1.9.1.1.1.</span> <span class="toc-text">批量插入</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#%E6%89%8B%E5%8A%A8%E6%8F%90%E4%BA%A4%E4%BA%8B%E5%8A%A1"><span class="toc-number">1.9.1.1.2.</span> <span class="toc-text">手动提交事务</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#%E4%B8%BB%E9%94%AE%E9%A1%BA%E5%BA%8F%E6%8F%92%E5%85%A5"><span class="toc-number">1.9.1.1.3.</span> <span class="toc-text">主键顺序插入</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#%E5%A4%A7%E6%89%B9%E9%87%8F%E6%8F%92%E5%85%A5%E6%95%B0%E6%8D%AE"><span class="toc-number">1.9.1.1.4.</span> <span class="toc-text">大批量插入数据</span></a></li></ol></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%B8%BB%E9%94%AE%E4%BC%98%E5%8C%96"><span class="toc-number">1.9.2.</span> <span class="toc-text">主键优化</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E7%BB%84%E7%BB%87%E6%96%B9%E5%BC%8F"><span class="toc-number">1.9.2.1.</span> <span class="toc-text">数据组织方式</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%B8%BB%E9%94%AE%E8%AE%BE%E8%AE%A1%E5%8E%9F%E5%88%99"><span class="toc-number">1.9.2.2.</span> <span class="toc-text">主键设计原则</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#Order-by%E4%BC%98%E5%8C%96"><span class="toc-number">1.9.3.</span> <span class="toc-text">Order by优化</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#group-by%E4%BC%98%E5%8C%96"><span class="toc-number">1.9.4.</span> <span class="toc-text">group by优化</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#limit%E4%BC%98%E5%8C%96"><span class="toc-number">1.9.5.</span> <span class="toc-text">limit优化</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#count%E4%BC%98%E5%8C%96"><span class="toc-number">1.9.6.</span> <span class="toc-text">count优化</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#update%E4%BC%98%E5%8C%96"><span class="toc-number">1.9.7.</span> <span class="toc-text">update优化</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E8%A7%86%E5%9B%BE-x2F-%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B-x2F-%E8%A7%A6%E5%8F%91%E5%99%A8"><span class="toc-number">1.10.</span> <span class="toc-text">视图&#x2F;存储过程&#x2F;触发器</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%A7%86%E5%9B%BE"><span class="toc-number">1.10.1.</span> <span class="toc-text">视图</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E8%A7%86%E5%9B%BE%E7%9A%84%E4%BD%9C%E7%94%A8"><span class="toc-number">1.10.1.1.</span> <span class="toc-text">视图的作用</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B"><span class="toc-number">1.10.2.</span> <span class="toc-text">存储过程</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E7%89%B9%E7%82%B9"><span class="toc-number">1.10.2.1.</span> <span class="toc-text">特点</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%9F%BA%E6%9C%AC%E8%AF%AD%E6%B3%95-1"><span class="toc-number">1.10.2.2.</span> <span class="toc-text">基本语法</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%8F%98%E9%87%8F"><span class="toc-number">1.10.2.3.</span> <span class="toc-text">变量</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#%E7%94%A8%E6%88%B7%E8%87%AA%E5%AE%9A%E4%B9%89%E5%8F%98%E9%87%8F"><span class="toc-number">1.10.2.3.1.</span> <span class="toc-text">用户自定义变量</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#%E5%B1%80%E9%83%A8%E5%8F%98%E9%87%8F"><span class="toc-number">1.10.2.3.2.</span> <span class="toc-text">局部变量</span></a></li></ol></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%8F%82%E6%95%B0"><span class="toc-number">1.10.2.4.</span> <span class="toc-text">参数</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#case"><span class="toc-number">1.10.2.5.</span> <span class="toc-text">case</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#while"><span class="toc-number">1.10.2.6.</span> <span class="toc-text">while</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#repeat"><span class="toc-number">1.10.2.7.</span> <span class="toc-text">repeat</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#loop"><span class="toc-number">1.10.2.8.</span> <span class="toc-text">loop</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E6%B8%B8%E6%A0%87"><span class="toc-number">1.10.2.9.</span> <span class="toc-text">游标</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E6%9D%A1%E4%BB%B6%E5%A4%84%E7%90%86%E7%A8%8B%E5%BA%8F%EF%BC%88handler%EF%BC%89"><span class="toc-number">1.10.2.10.</span> <span class="toc-text">条件处理程序（handler）</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AD%98%E5%82%A8%E5%87%BD%E6%95%B0"><span class="toc-number">1.10.3.</span> <span class="toc-text">存储函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%A7%A6%E5%8F%91%E5%99%A8"><span class="toc-number">1.10.4.</span> <span class="toc-text">触发器</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%BB%8B%E7%BB%8D"><span class="toc-number">1.10.4.1.</span> <span class="toc-text">介绍</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E8%AF%AD%E6%B3%95"><span class="toc-number">1.10.4.2.</span> <span class="toc-text">语法</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E9%94%81"><span class="toc-number">1.11.</span> <span class="toc-text">锁</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%85%A8%E5%B1%80%E9%94%81"><span class="toc-number">1.11.1.</span> <span class="toc-text">全局锁</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E7%89%B9%E7%82%B9-1"><span class="toc-number">1.11.1.1.</span> <span class="toc-text">特点</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%A1%A8%E7%BA%A7%E9%94%81"><span class="toc-number">1.11.2.</span> <span class="toc-text">表级锁</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#1-%E8%A1%A8%E9%94%81"><span class="toc-number">1.11.2.1.</span> <span class="toc-text">1.表锁</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#2-%E5%85%83%E6%95%B0%E6%8D%AE%E9%94%81%EF%BC%88meta-data-lock%EF%BC%8CMDL%EF%BC%89"><span class="toc-number">1.11.2.2.</span> <span class="toc-text">2.元数据锁（meta data lock，MDL）</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-%E6%84%8F%E5%90%91%E9%94%81"><span class="toc-number">1.11.2.3.</span> <span class="toc-text">3.意向锁</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%A1%8C%E7%BA%A7%E9%94%81"><span class="toc-number">1.11.3.</span> <span class="toc-text">行级锁</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#InnoDB%E5%BC%95%E6%93%8E"><span class="toc-number">1.12.</span> <span class="toc-text">InnoDB引擎</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E9%80%BB%E8%BE%91%E5%AD%98%E5%82%A8%E7%BB%93%E6%9E%84"><span class="toc-number">1.12.1.</span> <span class="toc-text">逻辑存储结构</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9E%B6%E6%9E%84"><span class="toc-number">1.12.2.</span> <span class="toc-text">架构</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%86%85%E5%AD%98%E7%BB%93%E6%9E%84"><span class="toc-number">1.12.2.1.</span> <span class="toc-text">内存结构</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#Buffer-Pool"><span class="toc-number">1.12.2.1.1.</span> <span class="toc-text">Buffer Pool</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#Change-Buffer"><span class="toc-number">1.12.2.1.2.</span> <span class="toc-text">Change Buffer</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#%E8%87%AA%E9%80%82%E5%BA%94hash"><span class="toc-number">1.12.2.1.3.</span> <span class="toc-text">自适应hash</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#Log-Buffer"><span class="toc-number">1.12.2.1.4.</span> <span class="toc-text">Log Buffer</span></a></li></ol></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E7%A3%81%E7%9B%98%E7%BB%93%E6%9E%84"><span class="toc-number">1.12.2.2.</span> <span class="toc-text">磁盘结构</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BA%8B%E5%8A%A1%E5%8E%9F%E7%90%86"><span class="toc-number">1.12.3.</span> <span class="toc-text">事务原理</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#redo-log"><span class="toc-number">1.12.3.1.</span> <span class="toc-text">redo log</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#undo-log"><span class="toc-number">1.12.3.2.</span> <span class="toc-text">undo log</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#MVCC"><span class="toc-number">1.12.4.</span> <span class="toc-text">MVCC</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E8%AE%B0%E5%BD%95%E4%B8%AD%E7%9A%84%E9%9A%90%E8%97%8F%E5%AD%97%E6%AE%B5"><span class="toc-number">1.12.4.1.</span> <span class="toc-text">记录中的隐藏字段</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#undo-log%E6%97%A5%E5%BF%97"><span class="toc-number">1.12.4.2.</span> <span class="toc-text">undo log日志</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#readview"><span class="toc-number">1.12.4.3.</span> <span class="toc-text">readview</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#RC"><span class="toc-number">1.12.4.3.1.</span> <span class="toc-text">RC</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#RR"><span class="toc-number">1.12.4.3.2.</span> <span class="toc-text">RR</span></a></li></ol></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#MySQL%E7%B3%BB%E7%BB%9F%E7%AE%A1%E7%90%86"><span class="toc-number">1.13.</span> <span class="toc-text">MySQL系统管理</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%B3%BB%E7%BB%9F%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">1.13.1.</span> <span class="toc-text">系统数据库</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%B8%B8%E7%94%A8%E5%B7%A5%E5%85%B7"><span class="toc-number">1.13.2.</span> <span class="toc-text">常用工具</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#mysql-1"><span class="toc-number">1.13.2.1.</span> <span class="toc-text">mysql</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#mysqladmin"><span class="toc-number">1.13.2.2.</span> <span class="toc-text">mysqladmin</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#mysqlbinlog"><span class="toc-number">1.13.2.3.</span> <span class="toc-text">mysqlbinlog</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#mysqlshow"><span class="toc-number">1.13.2.4.</span> <span class="toc-text">mysqlshow</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#mysqldump"><span class="toc-number">1.13.2.5.</span> <span class="toc-text">mysqldump</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#mysqlimport-x2F-source"><span class="toc-number">1.13.2.6.</span> <span class="toc-text">mysqlimport&#x2F;source</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#Note"><span class="toc-number">1.14.</span> <span class="toc-text">Note</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#MySQL-%E4%B8%AD%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB-RC-%E4%B8%8E-RR-%E7%9A%84%E5%8C%BA%E5%88%AB"><span class="toc-number">1.14.1.</span> <span class="toc-text">MySQL 中隔离级别 RC 与 RR 的区别</span></a></li></ol></li></ol></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>最新文章</span></div><div class="aside-list"><div class="aside-list-item"><a class="thumbnail" href="/mundane/2023/04/27/mysql-study/" title="mysql学习"><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/img/suzume.png" onerror="this.onerror=null;this.src='/mundane/img/404.jpg'" alt="mysql学习"/></a><div class="content"><a class="title" href="/mundane/2023/04/27/mysql-study/" title="mysql学习">mysql学习</a><time datetime="2023-04-27T15:01:33.000Z" title="发表于 2023-04-27 23:01:33">2023-04-27</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/mundane/2023/04/27/hadoop-study/" title="hadoop学习"><img src="https://mundane-1317827053.cos.ap-chengdu.myqcloud.com/img/67190321_p0.png" onerror="this.onerror=null;this.src='/mundane/img/404.jpg'" alt="hadoop学习"/></a><div class="content"><a class="title" href="/mundane/2023/04/27/hadoop-study/" title="hadoop学习">hadoop学习</a><time datetime="2023-04-27T14:47:36.000Z" title="发表于 2023-04-27 22:47:36">2023-04-27</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/mundane/2023/04/16/linux%E5%AD%A6%E4%B9%A0/" title="linux学习"><img src="/mundane/img/twist.png" onerror="this.onerror=null;this.src='/mundane/img/404.jpg'" alt="linux学习"/></a><div class="content"><a class="title" href="/mundane/2023/04/16/linux%E5%AD%A6%E4%B9%A0/" title="linux学习">linux学习</a><time datetime="2023-04-16T14:52:19.000Z" title="发表于 2023-04-16 22:52:19">2023-04-16</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/mundane/2023/04/15/hello-world/" title="Hello World"><img src="/mundane/img/girl.jpg" onerror="this.onerror=null;this.src='/mundane/img/404.jpg'" alt="Hello World"/></a><div class="content"><a class="title" href="/mundane/2023/04/15/hello-world/" title="Hello World">Hello World</a><time datetime="2023-04-15T12:52:02.200Z" title="发表于 2023-04-15 20:52:02">2023-04-15</time></div></div></div></div></div></div></main><footer id="footer"><div id="footer-wrap"><div class="copyright">&copy;2023 By frode</div><div class="footer_custom_text">1024</div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><a id="to_comment" href="#post-comment" title="直达评论"><i class="fas fa-comments"></i></a><button id="go-up" type="button" title="回到顶部"><span class="scroll-percent"></span><i class="fas fa-arrow-up"></i></button></div></div><div><script src="/mundane/js/utils.js"></script><script src="/mundane/js/main.js"></script><script src="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox/fancybox.umd.min.js"></script><script src="https://cdn.jsdelivr.net/npm/vanilla-lazyload/dist/lazyload.iife.min.js"></script><script src="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.js"></script><div class="js-pjax"><script>if (!window.MathJax) {
  window.MathJax = {
    tex: {
      inlineMath: [ ['$','$'], ["\\(","\\)"]],
      tags: 'ams'
    },
    chtml: {
      scale: 1.1
    },
    options: {
      renderActions: {
        findScript: [10, doc => {
          for (const node of document.querySelectorAll('script[type^="math/tex"]')) {
            const display = !!node.type.match(/; *mode=display/)
            const math = new doc.options.MathItem(node.textContent, doc.inputJax[0], display)
            const text = document.createTextNode('')
            node.parentNode.replaceChild(text, node)
            math.start = {node: text, delim: '', n: 0}
            math.end = {node: text, delim: '', n: 0}
            doc.math.push(math)
          }
        }, ''],
        insertScript: [200, () => {
          document.querySelectorAll('mjx-container').forEach(node => {
            if (node.hasAttribute('display')) {
              btf.wrap(node, 'div', { class: 'mathjax-overflow' })
            } else {
              btf.wrap(node, 'span', { class: 'mathjax-overflow' })
            }
          });
        }, '', false]
      }
    }
  }
  
  const script = document.createElement('script')
  script.src = 'https://cdn.jsdelivr.net/npm/mathjax/es5/tex-mml-chtml.min.js'
  script.id = 'MathJax-script'
  script.async = true
  document.head.appendChild(script)
} else {
  MathJax.startup.document.state(0)
  MathJax.texReset()
  MathJax.typesetPromise()
}</script><script>function loadValine () {
  function initValine () {
    const valine = new Valine(Object.assign({
      el: '#vcomment',
      appId: 'pgTqrs90s3uW64aIgVnjZkoo-gzGzoHsz',
      appKey: 'BlDuaZ3JC6jkoZ5xHIpAUJbH',
      avatar: 'monsterid',
      serverURLs: '',
      emojiMaps: "",
      path: window.location.pathname,
      visitor: false
    }, null))
  }

  if (typeof Valine === 'function') initValine() 
  else getScript('/mundane/js/Valine.min.js').then(initValine)
}

if ('Valine' === 'Valine' || !true) {
  if (true) btf.loadComment(document.getElementById('vcomment'),loadValine)
  else setTimeout(loadValine, 0)
} else {
  function loadOtherComment () {
    loadValine()
  }
}</script></div><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script><div id="local-search"><div class="search-dialog"><nav class="search-nav"><span class="search-dialog-title">搜索</span><span id="loading-status"></span><button class="search-close-button"><i class="fas fa-times"></i></button></nav><div class="is-center" id="loading-database"><i class="fas fa-spinner fa-pulse"></i><span>  数据库加载中</span></div><div class="search-wrap"><div id="local-search-input"><div class="local-search-box"><input class="local-search-box--input" placeholder="搜索文章" type="text"/></div></div><hr/><div class="no-result" id="local-search-results"></div><div id="local-search-stats-wrap"></div></div></div><div id="search-mask"></div><script src="/mundane/js/search/local-search.js"></script></div></div></body></html>